﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using System.Text;
using System.IO;

using PdfSharp;
using PdfSharp.Drawing;
using PdfSharp.Pdf;
using PdfSharp.Pdf.IO;
using CE.Mail;
using CE.Image;
using CE.Global;
using CE.Database;
using CE.CreditCard;
using CE.User;
using CE.PostCode;
using CE.PersonalAccident;
using CE.Provider;
using CE.Security;
using CE.InputOut;
using CE.CarInsurance;
using CE.TravelInsurance;
using CE.Log;

public partial class etiqa_paymentmsg : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            lblMessage.Text = "";

            if (Request.Params["authresp"] != null && Request.Params["ordernumber"] != null && Request.Params["email"] != null)
            {
                string _authresp = Request.Params["authresp"].ToString();
                string _ordernumber = Request.Params["ordernumber"].ToString();
                string _authcode = "";
                string _authdt = "";
                string _cctref = "";
                string _amount = "";
                string _cemail = "";
                if (Request.Params["authcode"] != null)
                {
                    _authcode = Request.Params["authcode"].ToString();
                }

                if (Request.Params["authdt"] != null)
                {
                    _authdt = Request.Params["authdt"].ToString();
                }

                if (Request.Params["cctref"] != null)
                {
                    _cctref = Request.Params["cctref"].ToString();
                }

                if (Request.Params["amount"] != null)
                {
                    _amount = Request.Params["amount"].ToString();
                }

                _cemail = Request.Params["email"].ToString();
                string _email = Request.Params["email"].ToString();

                InsertEtiqaPaymentGateWayReply(_ordernumber, _authresp, _authcode, _authdt, _cctref, _amount, _cemail);

                //RENEWAL - Phyo @ 06 Sep 2011
                CarInsuranceRenewal _renewal = new CarInsuranceRenewal();
                CarInsuranceRenewalDB _renewaldb = new CarInsuranceRenewalDB();
                _renewal = _renewaldb.GetItemByOrderNo(_ordernumber);
                if (_authresp == "OK" && _renewal != null && _renewal.CarRenewalID > 0)
                {
                    _renewal.Info1 = _renewal.LastYearPolicyNo;
                    _renewal.Info2 = _cemail;
                    _renewal.Info3 = _authcode;
                    _renewal.Info4 = _authdt;
                    _renewal.Info5 = _cctref;
                    _renewal.Info6 = _amount;

                    if (_renewaldb.Insert(_renewal) > 0)
                    {
                        string _toEmailAddress = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCIRenew_TO");
                        string _ccEmailAddress = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCIRenew_CC");
                        string _bccEmailAddress = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCIRenew_BCC");
                        System.Text.StringBuilder _strMailBody = new System.Text.StringBuilder();
                        _strMailBody.Append("Dear eTiQa Insurance,");
                        _strMailBody.Append("<br/><br/>Please check following Policy Number and Payment Reference for Car Insurance Renewal,<br/>");
                        _strMailBody.Append("<table>");
                        _strMailBody.Append("<tr><td><b>Vehicle No</b></td><td>:</td><td>" + _renewal.VehicleNo + "</td></tr>");
                        _strMailBody.Append("<tr><td><b>Policy Number</b></td><td>:</td><td>" + _renewal.LastYearPolicyNo + "</td></tr>");
                        _strMailBody.Append("<tr><td><b>Payment Reference</b></td><td>:</td><td>" + _renewal.OrderNo + "</td></tr>");
                        _strMailBody.Append("<tr><td><b>Renewal Premium ($)</b></td><td>:</td><td>" + _renewal.Info6 + "</td></tr>");//Sakthi on 22 Feb 2012
                        _strMailBody.Append("</table>");
                        _strMailBody.Append("<br/><br/>Best Regards,");
                        _strMailBody.Append("<br/>Your CompareXpress Team");
                        _strMailBody.Append("<br/>Customer Support - SG/International");
                        //CE.Mail.MyMailManager.SendCRMMessage(_toEmailAddress, _ccEmailAddress, _bccEmailAddress, "<Renewal> Car Insurance (Policy No." + _renewal.LastYearPolicyNo + ") from CompareXpress.com", _strMailBody.ToString());
                        if (!CE.Mail.MyMailManager.SendCARRenewalMessage(_toEmailAddress, _ccEmailAddress, _bccEmailAddress, "<Renewal> Car Insurance (Policy No." + _renewal.LastYearPolicyNo + ") from CompareXpress.com", _strMailBody.ToString()))
                        //CE.Mail.MyMailManager.SendCARRenewalMessage(_toEmailAddress, _ccEmailAddress, _bccEmailAddress, "<Renewal> Car Insurance (Policy No." + _renewal.LastYearPolicyNo + ") from CompareXpress.com", _strMailBody.ToString());
                        {
                            InsertErrorLog(_renewal.CarRenewalID, _renewal.CarID, _renewal.ProviderCompanyID, "CI", _ordernumber, "Etiqa Car Renewal Customer mail sending Failed.");
                        }
                                
                        Response.Redirect("paymentmsg.aspx?status=OK&refno=" + _ordernumber + "&policyno=" + _renewal.LastYearPolicyNo);
                    }
                    else
                    {
                        Response.Redirect("paymentmsg.aspx?status=FAIL");
                    }
                }
                //END RENEWAL - Phyo @ 06 Sep 2011
                ShowMessage(_authresp);
                //_authresp = "OK";

                if (_authresp == "OK")
                {
                    string CXProduct = CheckProduct(_ordernumber);

                    if (CXProduct == "PA")
                    {
                        Int64 _newPolicyNumberId = 0;
                        string _newPolicyNumber = "";
                        string _customername = "";
                        //_email += ";" + CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaPA_TO");
                        string _emailto = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaPA_TO");
                        string _emailcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaPA_CC");
                        string _emailbcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaPA_BCC");

                        Int64 _statusresult = 0;

                        EtiqaProposal _etiqataproposal = new EtiqaProposal();
                        EtiqaProposalDB _etiqataproposaldb = new EtiqaProposalDB();

                        _etiqataproposal = _etiqataproposaldb.GetPAMoreDetail(_ordernumber);

                        if (_etiqataproposal != null)
                        {
                            string convertno = General.GetRefNumber(_etiqataproposal.PAId);
                            string _fileName = "PAE" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
                            string _filePath = GetFilePath();
                            //_FilePath += _fileName + ".xls";

                            _etiqataproposal.OrderNo = _ordernumber;
                            _etiqataproposal.Info10 = _filePath + _fileName + ".xls";

                            _statusresult = _etiqataproposaldb.UpdateStatus(_etiqataproposal);

                            if (_statusresult > 0)
                            {
                                UpdateConfirmationPA(_etiqataproposal);

                                string[] filelist = GeneratePDFPA(_ordernumber, out _customername, out _newPolicyNumberId, out _newPolicyNumber);
                                if (filelist.Length == 2 && _newPolicyNumberId > 0)
                                {
                                    EtiqaPAPolicyNumberDB _policyNumberDb = new EtiqaPAPolicyNumberDB();
                                    if (_policyNumberDb.UpdateIssued(_newPolicyNumberId, _ordernumber, _authcode, _authdt, _cctref, _cemail, _amount) > 0)
                                    {
                                        if (filelist[0] != "" && filelist[1] != "")
                                        {
                                            if (SendEmailWithAttachPA(filelist, _email, _emailcc, _emailbcc, _customername, _ordernumber, _newPolicyNumber))
                                            {
                                                Response.Redirect("paymentmsg.aspx?status=OK");
                                            }
                                            else
                                            {
                                                //lblMessage.Text += "<br />Send email with attachments failed!";
                                                InsertErrorLog(_etiqataproposal.PAProposalId, _etiqataproposal.PAId, _etiqataproposal.PACompanyId, "PA", _ordernumber, "Send email with attachments failed!");
                                            }
                                        }
                                        else
                                        {
                                            //lblMessage.Text += "<br />PDF Files are not completed.";
                                            InsertErrorLog(_etiqataproposal.PAProposalId, _etiqataproposal.PAId, _etiqataproposal.PACompanyId, "PA", _ordernumber, "PDF Files are not completed.");
                                        }
                                    }
                                    else
                                    {
                                        //lblMessage.Text += "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString();
                                        InsertErrorLog(_etiqataproposal.PAProposalId, _etiqataproposal.PAId, _etiqataproposal.PACompanyId, "PA", _ordernumber, "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString());
                                    }
                                }
                                else
                                {
                                    //lblMessage.Text += "<br />PDF Files are completed or Policy Number did not generated.";
                                    InsertErrorLog(_etiqataproposal.PAProposalId, _etiqataproposal.PAId, _etiqataproposal.PACompanyId, "PA", _ordernumber, "PDF Files are completed or Policy Number did not generated.");
                                }
                            }
                            else
                            {
                                //lblMessage.Text = "Can't update payment status.";
                                InsertErrorLog(_etiqataproposal.PAProposalId, _etiqataproposal.PAId, _etiqataproposal.PACompanyId, "PA", _ordernumber, "Can't update payment status.");
                            }
                        }
                        else
                        {

                            GetUserAccounts(_ordernumber);
                            Response.Redirect("paymentmsg.aspx?status=FAIL");
                        }
                    }
                    else if (CXProduct == "CI")
                    {
                        Int64 _newPolicyNumberId = 0;
                        string _newPolicyNumber = "";
                        string _customername = "";
                        //_email += ";" + CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_TO");
                        string _emailto = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_TO");
                        string _emailcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_CC");
                        string _emailbcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_BCC");

                        //_ordernumber = "20100813001478000015";
                        Int64 _statusresult = 0;
                        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
                        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
                        _etiqacarmoredetail = _etiqacarmoredetaildb.GetCarMoreDetail(_ordernumber);

                        if (_etiqacarmoredetail != null)
                        {
                            string convertno = General.GetRefNumber(_etiqacarmoredetail.CECarId);
                            string _fileName = "E" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
                            string _filePath = GetFilePath();
                            //_FilePath += _fileName + ".xls";

                            _etiqacarmoredetail.OrderNo = _ordernumber;
                            _etiqacarmoredetail.Info10 = _filePath + _fileName + ".xls";

                            _statusresult = _etiqacarmoredetaildb.UpdateStatus(_etiqacarmoredetail);

                            if (_statusresult > 0)
                            {
                                UpdateConfirmationCAR(_etiqacarmoredetail);

                                string[] filelist = GeneratePDFCAR(_ordernumber, out _customername, out _newPolicyNumberId, out _newPolicyNumber);

                                if (filelist.Length == 3 && _newPolicyNumberId > 0)
                                {
                                    EtiqaCarPolicyNumberDB _policyNumberDb = new EtiqaCarPolicyNumberDB();
                                    if (_policyNumberDb.UpdateIssued(_newPolicyNumberId, _ordernumber, _authcode, _authdt, _cctref, _cemail, _amount) > 0)
                                    {
                                        if (filelist[0] != "" && filelist[1] != "" && filelist[2] != "")
                                        {
                                            if (SendEmailWithAttach(filelist, _email, _emailcc, _emailbcc, _customername, _ordernumber, _newPolicyNumber))
                                            {
                                                //Sakthi on 18 Mar 2011 -- start
                                                string _emailTitle = "Proposal Form submission via CompareXpress (Quote RefNo. : E" + convertno + ") at " + CE.Global.General.GetUTCDateTime().ToString("dd/MM/yyyy");
                                                string _emailBody = "";
                                                string[] _fileList = new string[1];
                                                string _toemail = "";
                                                string _ccemail = "";
                                                string _bccemail = "";
                                                string _contactusaddress = "";
                                                string _contactusemail = "";

                                                _toemail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("toemail_excel");
                                                _ccemail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("ccemail_excel");
                                                _bccemail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("bccemail_excel");
                                                _contactusaddress = CE.Configuration.MyConfigurationManager.GetAppSettingValue("contactusaddress_excel");
                                                _contactusemail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("contactusemail_excel");
                                                _contactusaddress = _contactusaddress.Replace(";", "<br/>");

                                                _emailBody = "Dear Etiqa, <br/><br/>Please find enclosed proposal form."
                                                             + "<br/><br/> If you have further queries, please contact us at: <br/>  <br/> "
                                                             + _contactusaddress
                                                             + "<br /> Email: "
                                                             + _contactusemail
                                                             + "<br/>Best Regards, <br/> <b>CompareXpress Team</b>";

                                                string _ExcelFilePath = GenerateProposalFormToEtiqa(_etiqacarmoredetail.CECarId, _filePath, _fileName);
                                                _fileList[0] = _ExcelFilePath.ToString();
                                                if (GMailManager.SendEmailByAttachedment(_emailTitle, _emailBody, _toemail, _ccemail, _bccemail, _fileList))
                                                {
                                                    Int64 _mailstatusresult = 0;
                                                    _mailstatusresult = _etiqacarmoredetaildb.UpdateMailSendingStatus(_etiqacarmoredetail.CECarMoreDetailId);
                                                    if (_mailstatusresult <= 0)
                                                    {
                                                        //lblMessage.Text += "<br />Can't update excel mail sending status.";
                                                        InsertErrorLog(_etiqacarmoredetail.CECarMoreDetailId, _etiqacarmoredetail.CECarId, _etiqacarmoredetail.ProviderCompanyId, "CI", _ordernumber, "Can't update excel mail sending status.");
                                                    }
                                                }
                                                else
                                                {
                                                    //lblMessage.Text += "<br />Send email with excel attachments failed!";
                                                    InsertErrorLog(_etiqacarmoredetail.CECarMoreDetailId, _etiqacarmoredetail.CECarId, _etiqacarmoredetail.ProviderCompanyId, "CI", _ordernumber, "Send email with excel attachments failed!");
                                                }
                                                //GenerateProposalFormToEtiqa(_etiqacarmoredetail.CECarId, _filePath, _fileName);
                                                //Sakthi on 18 Mar 2011 -- End

                                                Response.Redirect("paymentmsg.aspx?status=OK");
                                            }
                                            else
                                            {
                                                //lblMessage.Text += "<br />Send email with attachments failed!";
                                                InsertErrorLog(_etiqacarmoredetail.CECarMoreDetailId, _etiqacarmoredetail.CECarId, _etiqacarmoredetail.ProviderCompanyId, "CI", _ordernumber, "Send email with attachments failed!");
                                            }
                                        }
                                        else
                                        {
                                            //lblMessage.Text += "<br />PDF Files are not completed.";
                                            InsertErrorLog(_etiqacarmoredetail.CECarMoreDetailId, _etiqacarmoredetail.CECarId, _etiqacarmoredetail.ProviderCompanyId, "CI", _ordernumber, "PDF Files are not completed.");
                                        }
                                    }
                                    else
                                    {
                                        //lblMessage.Text += "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString();
                                        InsertErrorLog(_etiqacarmoredetail.CECarMoreDetailId, _etiqacarmoredetail.CECarId, _etiqacarmoredetail.ProviderCompanyId, "CI", _ordernumber, "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString());
                                    }
                                }
                                else
                                {
                                    //lblMessage.Text += "<br />PDF Files are completed or Policy Number did not generated.";
                                    InsertErrorLog(_etiqacarmoredetail.CECarMoreDetailId, _etiqacarmoredetail.CECarId, _etiqacarmoredetail.ProviderCompanyId, "CI", _ordernumber, "PDF Files are completed or Policy Number did not generated.");
                                }
                            }
                            else
                            {
                                //lblMessage.Text = "Can't update payment status.";
                                InsertErrorLog(_etiqacarmoredetail.CECarMoreDetailId, _etiqacarmoredetail.CECarId, _etiqacarmoredetail.ProviderCompanyId, "CI", _ordernumber, "Can't update payment status.");
                            }
                        }
                        else
                        {

                            GetUserAccounts(_ordernumber);
                            Response.Redirect("paymentmsg.aspx?status=FAIL");
                        }
                    }
                    else if (CXProduct == "TI") //12Dec2011 - Cheryl Lanot etiqa travel
                    {
                        Int64 _newPolicyNumberId = 0;
                        string _newPolicyNumber = "";
                        string _customername = "";
                        //_email += ";" + CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaTI_TO");
                        string _emailto = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaTI_TO");
                        string _emailcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaTI_CC");
                        string _emailbcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaTI_BCC");

                        Int64 _statusresult = 0;

                        TAMoreDetailEtiqa _etiqataproposal = new TAMoreDetailEtiqa();
                        TAMoreDetailEtiqaDB _etiqataproposaldb = new TAMoreDetailEtiqaDB();

                        _etiqataproposal = _etiqataproposaldb.GetTAMoreDetail(_ordernumber);

                        if (_etiqataproposal != null)
                        {
                            string convertno = General.GetRefNumber(_etiqataproposal.cetaId);
                            string _fileName = "TIE" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
                            string _filePath = GetFilePath();

                            _etiqataproposal.OrderNo = _ordernumber;
                            _etiqataproposal.Info10 = _filePath + _fileName + ".xls";

                            _statusresult = _etiqataproposaldb.UpdateStatus(_etiqataproposal);

                            if (_statusresult > 0)
                            {
                                UpdateConfirmationTI(_etiqataproposal);

                                //test variables
                                //EtiqaTAPolicyNumberDB poldb = new EtiqaTAPolicyNumberDB();
                                //EtiqaTAPolicyNumber podnum = new EtiqaTAPolicyNumber();
                                //podnum = poldb.GetNewPolicyNumber(30);
                                //_newPolicyNumberId = podnum.PolicyNumberId;
                                //_customername = "test only";
                                //_newPolicyNumber = podnum.PolicyNumber;

                                //test pa pdfs
                                //string[] filelist = new string[2];
                                //filelist[0] = "D:\\sharedloc\\pdfoutput\\etiqa\\2010Nov18\\Etiqa_Schedule_1.pdf";
                                //filelist[1] = "D:\\sharedloc\\pdfoutput\\etiqa\\2010Nov18\\Etiqa_TaxInvoice_1.pdf";

                                //188 test
                                //filelist[0] = "D:\\ShareLoc\\pdfoutput\\etiqa\\2011Aug25\\Etiqa_Schedule_198.pdf";
                                //filelist[1] = "D:\\ShareLoc\\pdfoutput\\etiqa\\2011Aug25\\Etiqa_TaxInvoice_198.pdf";


                                string[] filelist = GeneratePDFTI(_etiqataproposal.cetaId, _ordernumber, out _customername, out _newPolicyNumberId, out _newPolicyNumber);

                                if (filelist.Length > 0 && _newPolicyNumberId > 0)
                                {
                                    //test
                                    //Response.Redirect("paymentmsg.aspx?status=OK&tie=OK");

                                    EtiqaTAPolicyNumberDB _policyNumberDb = new EtiqaTAPolicyNumberDB();

                                    if (_policyNumberDb.UpdateIssued(_newPolicyNumberId, _ordernumber, _authcode, _authdt, _cctref, _cemail, _amount) > 0)
                                    {
                                        if (filelist[0] != "")
                                        {
                                            if (SendEmailWithAttachTI(filelist, _email, _emailcc, _emailbcc, _customername, _ordernumber, _newPolicyNumber))
                                            {
                                                //Sakthi - Hide on 23 Sep 2014 - Start
                                                //string _emailTitle = "Travel Proposal Form submission via CompareXpress (Quote RefNo. : TIE" + convertno + ") at " + CE.Global.General.GetUTCDateTime().ToString("dd/MM/yyyy");
                                                //string _emailBody = "";
                                                //string _contactusaddress = "";
                                                //string _contactusemail = "";
                                                //string[] excelfile = new string[1];

                                                //_contactusaddress = CE.Configuration.MyConfigurationManager.GetAppSettingValue("contactusaddress_excel");
                                                //_contactusemail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("contactusemail_excel");
                                                //_contactusaddress = _contactusaddress.Replace(";", "<br/>");

                                                //string _ExcelFilePath = GenerateTIExcel(_etiqataproposal.cetaId, _filePath, _fileName, _ordernumber, _newPolicyNumberId, _newPolicyNumber);
                                                //excelfile[0] = _ExcelFilePath.ToString();

                                                //_emailBody = "Dear Etiqa, <br/><br/>Please find enclosed proposal form."
                                                //                            + "<br/><br/> If you have further queries, please contact us at: <br/>  <br/> "
                                                //                            + _contactusaddress
                                                //                            + "<br /> Email: "
                                                //                            + _contactusemail
                                                //                            + "<br/>Best Regards, <br/> <b>CompareXpress Team</b>";

                                                //if (GMailManager.SendEmailByAttachedment(_emailTitle, _emailBody, _emailto, _emailcc, _emailbcc, excelfile))
                                                //{
                                                //    Response.Redirect("paymentmsg.aspx?status=OK&tie=OK");

                                                //}
                                                //else
                                                //{
                                                //    //lblMessage.Text += "<br />Send email with excel attachments failed!";
                                                //    InsertErrorLog(_etiqataproposal.cetaMoreDetailId, _etiqataproposal.cetaId, _etiqataproposal.ProviderCompanyId, "TI", _ordernumber, "Send email with excel attachments failed!");
                                                //}
                                                //Sakthi - Hide on 23 Sep 2014 - End
                                                Response.Redirect("paymentmsg.aspx?status=OK&tie=OK");
                                            }
                                            else
                                            {
                                                //lblMessage.Text += "<br />Send email with attachments failed!";
                                                InsertErrorLog(_etiqataproposal.cetaMoreDetailId, _etiqataproposal.cetaId, _etiqataproposal.ProviderCompanyId, "TI", _ordernumber, "Send email with attachments failed!");
                                            }
                                        }
                                        else
                                        {
                                            //lblMessage.Text += "<br />PDF File is not completed.";
                                            InsertErrorLog(_etiqataproposal.cetaMoreDetailId, _etiqataproposal.cetaId, _etiqataproposal.ProviderCompanyId, "TI", _ordernumber, "PDF File is not completed.");
                                        }
                                    }
                                    else
                                    {
                                        //lblMessage.Text += "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString();
                                        InsertErrorLog(_etiqataproposal.cetaMoreDetailId, _etiqataproposal.cetaId, _etiqataproposal.ProviderCompanyId, "TI", _ordernumber, "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString());
                                    }
                                }
                                else
                                {
                                    //lblMessage.Text += "<br />PDF File is completed or Policy Number did not generate.";
                                    InsertErrorLog(_etiqataproposal.cetaMoreDetailId, _etiqataproposal.cetaId, _etiqataproposal.ProviderCompanyId, "TI", _ordernumber, "PDF File is completed or Policy Number did not generate.");
                                }
                            }
                            else
                            {
                                //lblMessage.Text = "Can't update payment status.";
                                InsertErrorLog(_etiqataproposal.cetaMoreDetailId, _etiqataproposal.cetaId, _etiqataproposal.ProviderCompanyId, "TI", _ordernumber, "Can't update payment status.");
                            }
                        }
                        else
                        {

                            Response.Redirect("paymentmsg.aspx?status=FAIL");
                        }
                    }
                }
                //end ifend
            }
            else if (Request.Params["status"] != null)
            {
                if (Request.Params["status"] == "OK" && Request.Params["refno"] == null && Request.Params["tie"] != null)
                {
                    ShowMessage("OK");
                }
                else
                {
                    if (Request.Params["status"] == "OK" && Request.Params["refno"] == null)
                    {
                        ShowMessage("OK");
                        lblMessage.Text += "<br />Please check your email for your Insurance documents.";
                    }
                    else if (Request.Params["status"] == "OK" && Request.Params["refno"] != null)
                    {
                        ShowMessage("OK");
                        lblMessage.Text += "<br />Payment Ref No. : " + Request.Params["refno"].ToString();
                        if (Request.Params["policyno"] != null)
                        {
                            lblMessage.Text += "<br />Policy No. : " + Request.Params["policyno"].ToString();
                        }
                    }
                    else if (Request.Params["status"] == "FAIL")
                    {
                        ShowMessage("FAIL");
                    }
                    else
                    {
                        Response.Redirect("~/Home.aspx");
                    }
                }
            }
            else
            {
                Response.Redirect("~/Home.aspx");
            }
        }
    }

    public bool SendEmailWithAttach(string[] filelist, string email, string emailCC, string emailBCC, string customername, string ordernumber, string policynumber)
    {
        bool _retValue = false;

        Uri myUri = new Uri(HttpContext.Current.Request.Url.ToString());

        string domainUrl = "http://" + myUri.Host;
        string _etiqaLogoPath = domainUrl;
        _etiqaLogoPath += CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaLogoPath");

        string mailBody = "<br>Dear " + customername.ToUpper() + ",<br><br>";
        mailBody += "<table><tr><td><strong>Policy Number</strong></td><td>:</td><td>" + policynumber + "</td></tr>";
        mailBody += "<tr><td><strong>Payment reference</strong></td><td>:</td><td>" + ordernumber + "</td></tr></table>";
        mailBody += "<br/><br/>Thank you for insuring with Etiqa Insurance Pte Ltd.";//Etiqa Berhad
        mailBody += "<br/><br/>We are pleased to enclose the policy documents as attached for your kind attention.";
        mailBody += "<br/><br />We confirm coverage effective from the insurance commencement date as specified in ";
        mailBody += "the policy documents. If the insurance is to commence today, the time of cover starts ";
        mailBody += "from the time of this email.";
        mailBody += "<br/><br/>If you have any queries, please do not hesitate to contact us at 6331 9222 or email to us at ";
        mailBody += "<a href='mailto:cxmotor@etiqa.com.sg'>cxmotor@etiqa.com.sg</a>";
        mailBody += "<br/><br/>Marketing Dept";
        mailBody += "<br/>Etiqa Insurance Pte Ltd";
        mailBody += "<br/><img src='" + _etiqaLogoPath + "' alt='Etiqa Insurance Logo' />";
        mailBody += "<br/><br/><br/><style='font-style:italic;'>This document is automatically generated. Please do not respond to this email.</style>";

        string mailTitle = "Confirmation of Coverage with Etiqa (Policy No. " + policynumber + ")";
        if (MyMailManager.SendEtiqaEmailByAttachedmentNew(customername, email, emailCC, emailBCC, mailTitle, mailBody, filelist))
        {
            _retValue = true;
        }
        else
        {
            _retValue = false;
        }

        return _retValue;
    }

    public bool SendEmailWithAttachPA(string[] filelist, string email, string emailCC, string emailBCC, string customername, string ordernumber, string policynumber)
    {
        bool _retValue = false;

        Uri myUri = new Uri(HttpContext.Current.Request.Url.ToString());

        string domainUrl = "http://" + myUri.Host;
        string _etiqaLogoPath = domainUrl;
        _etiqaLogoPath += CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaLogoPath");

        string mailBody = "<br>Dear " + customername.ToUpper() + ",<br><br>";
        mailBody += "<table><tr><td><strong>Policy Number</strong></td><td>:</td><td>" + policynumber + "</td></tr>";
        mailBody += "<tr><td><strong>Payment reference</strong></td><td>:</td><td>" + ordernumber + "</td></tr></table>";
        mailBody += "<br/><br/>Thank you for insuring with Etiqa Insurance Pte Ltd.";//Etiqa Berhad
        mailBody += "<br/><br/>We are pleased to enclose the policy documents as attached for your kind attention.";
        mailBody += "<br/><br />We confirm coverage effective from the insurance commencement date as specified in ";
        mailBody += "the policy documents. If the insurance is to commence today, the time of cover starts ";
        mailBody += "from the time of this email.";
        mailBody += "<br/><br/>If you have any queries, please do not hesitate to contact us at 6331 9205 or email to us at ";
        mailBody += "<a href='mailto:cxsupport@etiqa.com.sg'>cxsupport@etiqa.com.sg</a>";
        mailBody += "<br/><br/>Marketing Dept";
        mailBody += "<br/>Etiqa Insurance Pte Ltd";
        mailBody += "<br/><img src='" + _etiqaLogoPath + "' alt='Etiqa Insurance Logo' />";
        mailBody += "<br/><br/><br/><style='font-style:italic;'>This document is automatically generated. Please do not respond to this email.</style>";

        string mailTitle = "Confirmation of Coverage with Etiqa (Policy No. " + policynumber + ")";
        if (MyMailManager.SendEtiqaEmailByAttachedmentNew(customername, email, emailCC, emailBCC, mailTitle, mailBody, filelist))
        {
            _retValue = true;
        }
        else
        {
            _retValue = false;
        }

        return _retValue;
    }

    //12Dec2011 - Cheryl Lanot etiqa travel
    public bool SendEmailWithAttachTI(string[] filelist, string email, string emailCC, string emailBCC, string customername, string ordernumber, string policynumber)
    {
        bool _retValue = false;

        Uri myUri = new Uri(HttpContext.Current.Request.Url.ToString());

        string domainUrl = "http://" + myUri.Host;
        string _etiqaLogoPath = domainUrl;
        _etiqaLogoPath += CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaLogoPath");

        string mailBody = "<br>Dear " + customername.ToUpper() + ",<br><br>";
        mailBody += "<table><tr><td><strong>Policy Number</strong></td><td>:</td><td>" + policynumber + "</td></tr>";
        mailBody += "<tr><td><strong>Payment reference</strong></td><td>:</td><td>" + ordernumber + "</td></tr></table>";
        mailBody += "<br/><br/>Thank you for insuring with Etiqa Insurance Pte Ltd.";//Etiqa Berhad
        mailBody += "<br/><br/>We are pleased to enclose the policy documents as attached for your kind attention.";
        mailBody += "<br/><br />We confirm coverage effective from the insurance commencement date as specified in ";
        mailBody += "the policy documents. If the insurance is to commence today, the time of cover starts ";
        mailBody += "from the time of this email.";
        mailBody += "<br/><br/>If you have any queries, please do not hesitate to contact us at 6331 9205 or email to us at ";
        mailBody += "<a href='mailto:cxsupport@etiqa.com.sg'>cxsupport@etiqa.com.sg</a>";
        mailBody += "<br/><br/>Marketing Dept";
        mailBody += "<br/>Etiqa Insurance Pte Ltd";
        mailBody += "<br/><img src='" + _etiqaLogoPath + "' alt='Etiqa Insurance Logo' />";
        mailBody += "<br/><br/><br/><style='font-style:italic;'>This document is automatically generated. Please do not respond to this email.</style>";

        string mailTitle = "Confirmation of Coverage with Etiqa (Policy No. " + policynumber + ")";
        if (MyMailManager.SendEtiqaEmailByAttachedmentNew(customername, email, emailCC, emailBCC, mailTitle, mailBody, filelist))
        {
            _retValue = true;
        }
        else
        {
            _retValue = false;
        }

        return _retValue;
    }

    public void ShowMessage(string AUTH)
    {
        if (AUTH == "OK")
        {
            lblMessage.Text = "Transaction successful";
        }
        else
        {
            //lblMessage.Text = "Transaction Failed. <a href = '../etiqa_proposal_form.aspx'><b>Click Here</b></a> to try again!";
            lblMessage.Text = "Transaction Failed. <a href = '../../User/ViewResultHistory.aspx'><b>Click Here</b></a> to try again!";
        }
        //else if (AUTH == "NA")
        //{
        //    lblMessage.Text = "All other host errors";
        //}
        //else if (AUTH == "IC")
        //{
        //    lblMessage.Text = "Expired card, or stolen, lost or invalid card from bank responses";
        //}
        //else if (AUTH == "RE")
        //{
        //    lblMessage.Text = "Referral";
        //}
        //else if (AUTH == "3D")
        //{
        //    lblMessage.Text = "Problem with Visa 3D was encountered such as invalid password by the customer";
        //}
        //else if (AUTH == "it")
        //{
        //    lblMessage.Text = "Fraud transaction – duplicate record with different details e.g. same OrderNumber but different amount.";
        //}
        //else if (AUTH == "TO")
        //{
        //    lblMessage.Text = "Timeout or host not available/couldn’t connect/or Host is busy doing a settlement and is not available";
        //}
        //else if (AUTH == "IT")
        //{
        //    lblMessage.Text = "Bad Merchant/Merchant invalid/invalid transaction (e.g. Amt equals to 0)";
        //}
        //else if (AUTH == "ic")
        //{
        //    lblMessage.Text = "Bad card format – card not in Bin Range for that particular merchant’s terminal.";
        //}
        //else if (AUTH == "L5" || AUTH == "L6")
        //{
        //    lblMessage.Text = "Velocity checks failed on amounts allowed for that card";
        //}
        //else if (AUTH == "L4")
        //{
        //    lblMessage.Text = "Too many transactions for that card";
        //}
        //else if (AUTH == "L2" || AUTH == "L3")
        //{
        //    lblMessage.Text = "Velocity checks failed on amounts allowed for that Merchant";
        //}
        //else if (AUTH == "L1")
        //{
        //    lblMessage.Text = "Amount too large";
        //}
    }

    //12Dec2011 - Cheryl Lanot etiqa travel
    public void UpdateConfirmationTI(TAMoreDetailEtiqa _tamoredetailetiqa)
    {

        UserConfirmation _userconfirm = new UserConfirmation();
        UserConfirmationDB _userconfirmdb = new UserConfirmationDB();
        _userconfirm.ConfirmationType = "TA";
        _userconfirm.CurrentStep = 3;

        _userconfirm.UserSelectedId = 0;
        _userconfirm.FirstStepId = _tamoredetailetiqa.cetaId;
        _userconfirm.RefCEUserId = _tamoredetailetiqa.RefCEUserId;
        _userconfirm.IsConfirm = true;
        _userconfirm.ResultStatus = "Complete";
        _userconfirm.IsComplete = true;
        Int64 _result = _userconfirmdb.Insert(_userconfirm);

    }

    public void UpdateConfirmationPA(EtiqaProposal _etiqaproposal)
    {

        UserConfirmation _userconfirm = new UserConfirmation();
        UserConfirmationDB _userconfirmdb = new UserConfirmationDB();
        _userconfirm.ConfirmationType = "PA";
        _userconfirm.CurrentStep = 3;

        _userconfirm.UserSelectedId = 0;
        _userconfirm.FirstStepId = _etiqaproposal.PAId;
        _userconfirm.RefCEUserId = _etiqaproposal.RefCEUserId;
        _userconfirm.IsConfirm = true;
        _userconfirm.ResultStatus = "Complete";
        _userconfirm.IsComplete = true;
        Int64 _result = _userconfirmdb.Insert(_userconfirm);

    }

    public void UpdateConfirmationCAR(EtiqaCarMoreDetail _etiqacarmoredetail)
    {
        UserConfirmation _userconfirm = new UserConfirmation();
        UserConfirmationDB _userconfirmdb = new UserConfirmationDB();
        _userconfirm.ConfirmationType = "CI";
        _userconfirm.CurrentStep = 4;
        //_userconfirm.UserResultId = Convert.ToInt64(Session[General.CarResultId].ToString());
        _userconfirm.UserSelectedId = 0;//Convert.ToInt64(Session[General.CarResultId].ToString());
        _userconfirm.FirstStepId = _etiqacarmoredetail.CECarId;//Convert.ToInt64(Session[General.CarId].ToString());
        _userconfirm.RefCEUserId = _etiqacarmoredetail.RefCEUserId;//Convert.ToInt64(Session[General.UserId].ToString());
        _userconfirm.IsConfirm = true;
        _userconfirm.ResultStatus = "Complete";
        _userconfirm.IsComplete = true;
        Int64 _result = _userconfirmdb.Insert(_userconfirm);


    }

    public void InsertErrorLog(Int64 ProposalId, Int64 RefId, Int64 ProviderCompanyId, string ProductType, string OrderNo, string ErrorMessage)
    {
        ErrorLogDB _errorlogdb = new ErrorLogDB();
        ErrorLog _errorlog = new ErrorLog();

        _errorlog.ProposalId = ProposalId;
        _errorlog.RefId = RefId;
        _errorlog.ProviderCompanyId = ProviderCompanyId;
        _errorlog.ProductType = ProductType;
        _errorlog.OrderNo = OrderNo;
        _errorlog.ErrorMessage = ErrorMessage;

        Int64 _result = _errorlogdb.Insert(_errorlog);
        if (_result > 0)
        {
            string _emailmessage = "<br>Dear CX Team,<br><br>ComparExpress.com has error occured.";
            _emailmessage += "<br><br>ProposalId : " + _errorlog.ProposalId
                + "<br>RefId : " + _errorlog.RefId
                + "<br>ProviderCompanyId : " + _errorlog.ProviderCompanyId
                + "<br>ProductType : " + _errorlog.ProductType
                + "<br>OrderNo : " + _errorlog.OrderNo
                + "<br>ErrorMessage : " + _errorlog.ErrorMessage;

            MyMailManager.SendProposalErrorLog(_emailmessage);
            
        }
    }

    private void GetUserAccounts(string ORDERNO)
    {
        Int64 _UserId = 0;
        MyDatabase _db = new MyDatabase();
        string strSql = "";
        strSql = @"SELECT RefCEUserId FROM tblPAProposal_Etiqa WHERE OrderNo = " + ORDERNO;
        DataTable _dtResult = new DataTable();
        _UserId = Convert.ToInt64(_db.ExecuteScalarSqlStatement(strSql));
        Account _account = new Account();
        AccountDB _accountdb = new AccountDB();
        _account = _accountdb.GetAccount(_UserId);

        if (_account != null)
        {
            GetUserInformation(_account);
        }

        _account = null;
        _accountdb = null;
    }

    public string GetPremiumDetailsPA(string ORDERNO)
    {
        string _premium;
        _premium = "";

        DataSet _dsPremium = new DataSet();
        EtiqaProposal _etiqapaproposal = new EtiqaProposal();
        EtiqaProposalDB _etiqapaproposaldb = new EtiqaProposalDB();
        _dsPremium = _etiqapaproposaldb.GetPremiumDetails(ORDERNO);
        if (_dsPremium != null && _dsPremium.Tables[0].Rows.Count > 0)
        {
            _premium = _dsPremium.Tables[0].Rows[0]["PremiumDue"].ToString();

        }

        return _premium;
    }

    public string GetPremiumDetailsCAR(string ORDERNO)
    {
        string _premium, _totaldue, _premiumgst, _premiumdue, _annualpremium, _ncdproduct, _ncdpremium, _ncd, _basicanlpremium, _nameddr, _unnameddr, _productpercent, _ofdstatus, _ofdamount, _ofdpercent;
        _premium = ""; _totaldue = ""; _premiumgst = ""; _premiumdue = ""; _annualpremium = ""; _ncdproduct = ""; _ncdpremium = ""; _productpercent = ""; _ofdpercent = "";
        _ncd = ""; _basicanlpremium = ""; _nameddr = ""; _unnameddr = "0.00"; _ofdstatus = ""; _ofdamount = "0.00";

        string _awsamount, _drivercharge;
        _awsamount = ""; _drivercharge = "";

        string _discountedpremium, _promodiscount;
        _discountedpremium = ""; _promodiscount = "";

        DataSet _dsPremium = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        _dsPremium = _etiqacarmoredetaildb.GetPremiumDetails(ORDERNO);
        if (_dsPremium != null && _dsPremium.Tables[0].Rows.Count > 0)
        {
            _totaldue = _dsPremium.Tables[0].Rows[0]["TotalDue"].ToString();
            _premiumgst = _dsPremium.Tables[0].Rows[0]["PremiumGST"].ToString();
            _premiumdue = _dsPremium.Tables[0].Rows[0]["PremiumDue"].ToString();
            _annualpremium = _dsPremium.Tables[0].Rows[0]["AnnualPremium"].ToString();
            _ncdproduct = _dsPremium.Tables[0].Rows[0]["NCDProduct"].ToString();
            _ncdpremium = _dsPremium.Tables[0].Rows[0]["NCDPremium"].ToString();
            _ncd = _dsPremium.Tables[0].Rows[0]["NCD"].ToString();
            _basicanlpremium = _dsPremium.Tables[0].Rows[0]["BasicAnlPremium"].ToString();

            _nameddr = _dsPremium.Tables[0].Rows[0]["DriverExessAmt"].ToString();

            Double _dNameDriverAmt = Convert.ToDouble(_nameddr);
            _unnameddr = Convert.ToString(_dNameDriverAmt * 2);

            _productpercent = _dsPremium.Tables[0].Rows[0]["ProductPercent"].ToString();
            _ofdstatus = _dsPremium.Tables[0].Rows[0]["OFDStatus"].ToString();
            _ofdamount = _dsPremium.Tables[0].Rows[0]["OFDAmount"].ToString();
            _ofdpercent = _dsPremium.Tables[0].Rows[0]["OFDPercent"].ToString();

            _awsamount = _dsPremium.Tables[0].Rows[0]["AWSAmount"].ToString();
            _drivercharge = _dsPremium.Tables[0].Rows[0]["DriverCharge"].ToString();

            _discountedpremium = _dsPremium.Tables[0].Rows[0]["DiscountedPremium"].ToString();
            _promodiscount = _dsPremium.Tables[0].Rows[0]["PromoDiscount"].ToString();

        }
        if (_dsPremium != null && _dsPremium.Tables.Count > 1 && _dsPremium.Tables[1].Rows.Count > 0)
        {
            _unnameddr = Convert.ToSingle(_dsPremium.Tables[1].Rows[0]["ExcessAmount"].ToString()).ToString("f2");
        }
        _premium = _totaldue + "," + _premiumgst + "," + _premiumdue + "," + _annualpremium + "," + _ncdproduct + "," + _ncdpremium + ","
            + _ncd + "," + _basicanlpremium + "," + _nameddr + "," + _unnameddr + "," + _productpercent + "," + _ofdstatus + ","
            + _ofdamount + "," + _ofdpercent + "," + _awsamount + "," + _drivercharge + "," + _discountedpremium + "," + _promodiscount;

        return _premium;
    }

    public string[] GeneratePDFPA(string ORDERNO, out string customername, out Int64 policyNumberId, out string policyNumber)
    {
        CE.PersonalAccident.EtiqaPAInsurancePDFOutput _etiPdfOut = new CE.PersonalAccident.EtiqaPAInsurancePDFOutput();
        string[] fileList = new string[2];
        string Premium = GetPremiumDetailsPA(ORDERNO);
        string _AgentStatus = "No";
        DataSet _dsPolicy = new DataSet();
        EtiqaProposal _etiqapaproposal = new EtiqaProposal();
        EtiqaProposalDB _etiqapaproposaldb = new EtiqaProposalDB();
        EtiqaPAPolicyNumber _policynumber = new EtiqaPAPolicyNumber();
        EtiqaPAPolicyNumberDB _policynumberdb = new EtiqaPAPolicyNumberDB();

        _policynumber = _policynumberdb.GetNewPolicyNumber(30);
        policyNumberId = 0;
        policyNumber = "";
        customername = "";
        _dsPolicy = _etiqapaproposaldb.GetPolicyDetails(ORDERNO);


        if (_policynumber != null && _dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _AgentStatus = _dsPolicy.Tables[0].Rows[0]["AgentStatus"].ToString();
            _etiPdfOut.ProposalId = _dsPolicy.Tables[0].Rows[0]["PAProposalID"].ToString();
            _etiPdfOut.CustomerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            customername = _etiPdfOut.CustomerName;
            _etiPdfOut.CustomerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _etiPdfOut.CustomerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
            _etiPdfOut.PolicyNumber = _policynumber.PolicyNumber;
            policyNumberId = _policynumber.PolicyNumberId;
            policyNumber = _policynumber.PolicyNumber;
            _etiPdfOut.GSTRegNumber = "M90363266Y";
            _etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();

            _etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";
            if (_AgentStatus == "Yes")
            {
                _etiPdfOut.AcountAgency = "000597/ 000597";
                _etiPdfOut.Agency = "000597";
                _etiPdfOut.Account = "000597";
            }
            else if (_AgentStatus == "No")
            {
                _etiPdfOut.AcountAgency = "000598/ 000598";
                _etiPdfOut.Agency = "000598";
                _etiPdfOut.Account = "000598";
            }
            //_etiPdfOut.AcountAgency = "000598/ 00598";
            _etiPdfOut.CardType = "";
            _etiPdfOut.CardLastFourDigit = "";

            //_etiPdfOut.Agency = "000598/000598";
            _etiPdfOut.ClassOfPolicy = "e-Protect Insurance";
            //_etiPdfOut.Account = "000598";
            _etiPdfOut.IssuedOn = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString() + " in SINGAPORE";
            _etiPdfOut.ReplacePolicyNumber = "";
            _etiPdfOut.Client = "";
            _etiPdfOut.AcceptanceDate = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
            _etiPdfOut.PeriodInsuranceStart = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
            _etiPdfOut.PeriodInsuranceEnd = _dsPolicy.Tables[0].Rows[0]["EndDatewd"].ToString();

            _etiPdfOut.TotalDueInvoice = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString()));
            _etiPdfOut.isPromo = false;
            _etiPdfOut.NewPremium = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString()));
            //check for promo
            DataTable dtPromo = getPromoDetails(ORDERNO);
            if (dtPromo.Rows.Count > 0)
            {
                _etiPdfOut.isPromo = true;
                _etiPdfOut.TotalDueInvoice = String.Format("{0:0,0.00}", Convert.ToDouble(dtPromo.Rows[0]["AnnualPremium"].ToString()));
                _etiPdfOut.NewPremium = String.Format("{0:0,0.00}", Convert.ToDouble(dtPromo.Rows[0]["DiscountedPremium"].ToString()));
            }

            _etiPdfOut.PlanName = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            _etiPdfOut.AccidentalDeath = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.PermanentDisability = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalParalysis = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalLossOfSight = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalLossOfSpeech = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalLossOfHearingBoth = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            //_etiPdfOut.HospitalConfinement = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["TPDAdult"].ToString()));
            _etiPdfOut.HospitalConfinement = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["HCPDAdult"].ToString()));
            _etiPdfOut.OutpatientMedExp = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["MedExpAdult"].ToString()));

            _etiPdfOut.NRICNo = _dsPolicy.Tables[0].Rows[0]["NRICNo"].ToString();
            _etiPdfOut.DOB = (Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["DOB"].ToString())).ToString("dd/MM/yyyy");
            _etiPdfOut.Occupation = _dsPolicy.Tables[0].Rows[0]["Occupation"].ToString();

            //Modified on 20 Dec 2010 by Sakthi - start
            _etiPdfOut.ChildCount = _dsPolicy.Tables[0].Rows[0]["NoOfChild"].ToString();
            _etiPdfOut.SpouseStatus = _dsPolicy.Tables[0].Rows[0]["SpouseStatus"].ToString();

            _etiPdfOut.SpouseName = "";
            _etiPdfOut.SpouseNRICNo = "";
            _etiPdfOut.SpouseDOB = "";
            _etiPdfOut.SpouseOccupation = "";

            if (_etiPdfOut.SpouseStatus == "Yes")
            {
                if (_dsPolicy.Tables[1].Rows.Count > 0)
                {
                    _etiPdfOut.SpouseName = _dsPolicy.Tables[1].Rows[0]["SpouseName"].ToString();
                    _etiPdfOut.SpouseNRICNo = _dsPolicy.Tables[1].Rows[0]["SpouseNRICNo"].ToString();
                    _etiPdfOut.SpouseDOB = (Convert.ToDateTime(_dsPolicy.Tables[1].Rows[0]["SPDOB"].ToString())).ToString("dd/MM/yyyy");
                    _etiPdfOut.SpouseOccupation = _dsPolicy.Tables[1].Rows[0]["SpouseOccupation"].ToString();
                }
            }

            _etiPdfOut.ChildName1 = "";
            _etiPdfOut.ChildNRICNo1 = "";
            _etiPdfOut.ChildDOB1 = "";

            _etiPdfOut.ChildName2 = "";
            _etiPdfOut.ChildNRICNo2 = "";
            _etiPdfOut.ChildDOB2 = "";

            _etiPdfOut.ChildName3 = "";
            _etiPdfOut.ChildNRICNo3 = "";
            _etiPdfOut.ChildDOB3 = "";

            _etiPdfOut.ChildName4 = "";
            _etiPdfOut.ChildNRICNo4 = "";
            _etiPdfOut.ChildDOB4 = "";

            _etiPdfOut.ChildName5 = "";
            _etiPdfOut.ChildNRICNo5 = "";
            _etiPdfOut.ChildDOB5 = "";
            Int16 _NoofChilds = Convert.ToInt16(_etiPdfOut.ChildCount);
            if (_dsPolicy.Tables[2].Rows.Count > 0)
            {
                for (int i = 0; i < _NoofChilds; i++)
                {
                    if (i == 0)
                    {
                        _etiPdfOut.ChildName1 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo1 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB1 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 1)
                    {
                        _etiPdfOut.ChildName2 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo2 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB2 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 2)
                    {
                        _etiPdfOut.ChildName3 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo3 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB3 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 3)
                    {
                        _etiPdfOut.ChildName4 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo4 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB4 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 4)
                    {
                        _etiPdfOut.ChildName5 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo5 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB5 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                }
            }
            //Modified on 20 Dec 2010 by Sakthi - End

            if (!_etiPdfOut.GenerateAllOuput(out fileList))
            {
                fileList[0] = "";
                fileList[1] = "";
            }
        }
        else
        {
            fileList[0] = "";
            fileList[1] = "";
        }

        return fileList;
    }

    //public string[] GeneratePDFPA(string ORDERNO, out string customername, out Int64 policyNumberId, out string policyNumber)
    //{
    //    CE.PersonalAccident.EtiqaPAInsurancePDFOutput _etiPdfOut = new CE.PersonalAccident.EtiqaPAInsurancePDFOutput();
    //    string[] fileList = new string[2];
    //    string Premium = GetPremiumDetailsPA(ORDERNO);
    //    string _AgentStatus = "No";
    //    DataSet _dsPolicy = new DataSet();
    //    EtiqaProposal _etiqapaproposal = new EtiqaProposal();
    //    EtiqaProposalDB _etiqapaproposaldb = new EtiqaProposalDB();
    //    EtiqaPAPolicyNumber _policynumber = new EtiqaPAPolicyNumber();
    //    EtiqaPAPolicyNumberDB _policynumberdb = new EtiqaPAPolicyNumberDB();

    //    _policynumber = _policynumberdb.GetNewPolicyNumber(30);
    //    policyNumberId = 0;
    //    policyNumber = "";
    //    customername = "";
    //    _dsPolicy = _etiqapaproposaldb.GetPolicyDetails(ORDERNO);


    //    if (_policynumber != null && _dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
    //    {
    //        _AgentStatus = _dsPolicy.Tables[0].Rows[0]["AgentStatus"].ToString();
    //        _etiPdfOut.ProposalId = _dsPolicy.Tables[0].Rows[0]["PAProposalID"].ToString();
    //        _etiPdfOut.CustomerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
    //        customername = _etiPdfOut.CustomerName;
    //        _etiPdfOut.CustomerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
    //        _etiPdfOut.CustomerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
    //        _etiPdfOut.PolicyNumber = _policynumber.PolicyNumber;
    //        policyNumberId = _policynumber.PolicyNumberId;
    //        policyNumber = _policynumber.PolicyNumber;
    //        _etiPdfOut.GSTRegNumber = "M90363266Y";
    //        _etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();

    //        _etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";
    //        if (_AgentStatus == "Yes")
    //        {
    //            _etiPdfOut.AcountAgency = "000597/ 000597";
    //            _etiPdfOut.Agency = "000597";
    //            _etiPdfOut.Account = "000597";
    //        }
    //        else if (_AgentStatus == "No")
    //        {
    //            _etiPdfOut.AcountAgency = "000598/ 000598";
    //            _etiPdfOut.Agency = "000598";
    //            _etiPdfOut.Account = "000598";
    //        }           

    //        _etiPdfOut.CardType = "";
    //        _etiPdfOut.CardLastFourDigit = "";
    //        _etiPdfOut.ClassOfPolicy = "e-Protect Insurance";
    //        //_etiPdfOut.Account = "000598";
    //        _etiPdfOut.IssuedOn = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString() + " in SINGAPORE";
    //        _etiPdfOut.ReplacePolicyNumber = "";
    //        _etiPdfOut.Client = "";
    //        _etiPdfOut.AcceptanceDate = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
    //        _etiPdfOut.PeriodInsuranceStart = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
    //        _etiPdfOut.PeriodInsuranceEnd = _dsPolicy.Tables[0].Rows[0]["EndDatewd"].ToString();

    //        _etiPdfOut.TotalDueInvoice = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString()));

    //        _etiPdfOut.PlanName = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
    //        _etiPdfOut.AccidentalDeath = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
    //        _etiPdfOut.PermanentDisability = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
    //        _etiPdfOut.TotalParalysis = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
    //        _etiPdfOut.TotalLossOfSight = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
    //        _etiPdfOut.TotalLossOfSpeech = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
    //        _etiPdfOut.TotalLossOfHearingBoth = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
    //        _etiPdfOut.HospitalConfinement = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["TPDAdult"].ToString()));
    //        _etiPdfOut.OutpatientMedExp = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["MedExpAdult"].ToString()));

    //        _etiPdfOut.NRICNo = _dsPolicy.Tables[0].Rows[0]["NRICNo"].ToString();
    //        _etiPdfOut.DOB = (Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["DOB"].ToString())).ToString("dd/MM/yyyy");
    //        _etiPdfOut.Occupation = _dsPolicy.Tables[0].Rows[0]["Occupation"].ToString();


    //        if (!_etiPdfOut.GenerateAllOuput(out fileList))
    //        {
    //            fileList[0] = "";
    //            fileList[1] = "";
    //        }
    //    }
    //    else
    //    {
    //        fileList[0] = "";
    //        fileList[1] = "";
    //    }

    //    return fileList;
    //}

    public string[] GeneratePDFCAR(string ORDERNO, out string customername, out Int64 policyNumberId, out string policyNumber)
    {
        CE.CarInsurance.EtiqaCarInsurancePDFOutput _etiPdfOut = new CE.CarInsurance.EtiqaCarInsurancePDFOutput();
        string[] fileList = new string[3];
        string _premium = GetPremiumDetailsCAR(ORDERNO);
        string[] Premium = _premium.Split(',');
        string _cardriverList = GetCarDriverNameList(ORDERNO);
        string _AgentStatus = "No";
        string _cxcovertypeid = "0";

        DataSet _dsPolicy = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        EtiqaCarPolicyNumber _policynumber = new EtiqaCarPolicyNumber();
        EtiqaCarPolicyNumberDB _policynumberdb = new EtiqaCarPolicyNumberDB();
        _policynumber = _policynumberdb.GetNewPolicyNumber(30);
        policyNumberId = 0;
        policyNumber = "";
        customername = "";
        _dsPolicy = _etiqacarmoredetaildb.GetPolicyDetails(ORDERNO);


        if (_policynumber != null && _dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _AgentStatus = _dsPolicy.Tables[0].Rows[0]["AgentStatus"].ToString();
            _cxcovertypeid = _dsPolicy.Tables[0].Rows[0]["CXCoverTypeId"].ToString();

            _etiPdfOut.ProposalId = _dsPolicy.Tables[0].Rows[0]["CECarMoreDetailId"].ToString();
            _etiPdfOut.CustomerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            customername = _etiPdfOut.CustomerName;
            _etiPdfOut.CustomerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _etiPdfOut.CustomerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
            _etiPdfOut.PolicyNumber = _policynumber.PolicyNumber;
            policyNumberId = _policynumber.PolicyNumberId;
            policyNumber = _policynumber.PolicyNumber;
            _etiPdfOut.GSTRegNumber = "M90363266Y";
            _etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();

            _etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";

            _etiPdfOut.TotalAnnualPermium = _dsPolicy.Tables[0].Rows[0]["AnnualPremium"].ToString();
            _etiPdfOut.CardType = "";//_dsPolicy.Tables[0].Rows[0]["CardType"].ToString();
            _etiPdfOut.CardLastFourDigit = "";//_dsPolicy.Tables[0].Rows[0]["CreditCardNo"].ToString().Substring(8, 4);

            if (_dsPolicy.Tables[3].Rows.Count > 0)
            {
                _etiPdfOut.Etiqa20PercentPromo = _dsPolicy.Tables[3].Rows[0]["info1"].ToString();
            }

            if (_AgentStatus == "Yes")
            {
                _etiPdfOut.Agency = "000597";
                _etiPdfOut.Account = "000597";
            }
            else if (_AgentStatus == "No")
            {
                _etiPdfOut.Agency = "000598";
                _etiPdfOut.Account = "000598";
            }

            //if (Session[CE.Global.General.AgentId] != null)
            //{
            //    _etiPdfOut.Agency = "000597";
            //}
            //else
            //{
            //    _etiPdfOut.Agency = "000598";
            //}

            _etiPdfOut.ClassOfPolicy = "MOTOR PRIVATE CAR";

            _etiPdfOut.IssuedOn = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString() + " in SINGAPORE";
            _etiPdfOut.ReplacePolicyNumber = "";
            _etiPdfOut.Client = "";
            _etiPdfOut.AcceptanceDate = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();

            _etiPdfOut.PeriodInsuranceStart = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
            _etiPdfOut.PeriodInsuranceEnd = _dsPolicy.Tables[0].Rows[0]["EndDatewd"].ToString();

            _etiPdfOut.Occupation = _dsPolicy.Tables[0].Rows[0]["NofBusi"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["Description"].ToString();

            //Double _dBasicPremiumAmount = Convert.ToDouble(Premium[7].ToString());
            //_etiPdfOut.BasicPremiumAmount = "S$ " + _dBasicPremiumAmount.ToString("#,000.00");
            _etiPdfOut.BasicPremiumAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[7].ToString()));

            _etiPdfOut.LessNCDPercent = Premium[6].ToString() + "%";

            //Double _dLessNCDAmount = Convert.ToDouble(Premium[5].ToString());
            //_etiPdfOut.LessNCDAmount = "S$ " + _dLessNCDAmount.ToString("#,000.00"); 
            _etiPdfOut.LessNCDAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[5].ToString()));

            _etiPdfOut.NCDProtectionPremiumPercent = Premium[10].ToString();

            _etiPdfOut.IsOFD = Premium[11].ToString() == "0" ? false : true;
            _etiPdfOut.LessOFDAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[12].ToString()));
            _etiPdfOut.OFDPercent = Premium[13].ToString();
            //Double _dNCDProtectionPremiumAmount = Convert.ToDouble(Premium[4].ToString());
            //_etiPdfOut.NCDProtectionPremiumAmount = "S$ " + _dNCDProtectionPremiumAmount.ToString("#,000.00");
            if (Premium[4].ToString() != "0.00" && Premium[4].ToString() != "")
            {
                if (Convert.ToDouble(Premium[4].ToString()) > 0)
                {
                    _etiPdfOut.IsNCDProtector = true;
                }
                else
                {
                    _etiPdfOut.IsNCDProtector = false;
                }
                _etiPdfOut.NCDProtectionPremiumAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[4].ToString()));

            }

            if (Premium[14].ToString() != "0.00" && Premium[14].ToString() != "")
            {
                _etiPdfOut.LessAWSAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[14].ToString()));
            }

            if (Premium[15].ToString() != "0.00" && Premium[15].ToString() != "")
            {
                _etiPdfOut.AdditionalCharge = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[15].ToString()));
            }

            //_etiPdfOut.TotalAnnualPermium = "S$ " + string.Format("{0:0,0.00}",Premium[3].ToString());
            if (Premium[3].ToString() != "" && Premium[3].ToString() != "0.00")
            {
                _etiPdfOut.TotalAnnualPermium = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[3].ToString()));
            }

            if (Premium[2].ToString() != "" && Premium[2].ToString() != "0.00")
            {
                _etiPdfOut.PremiumDue = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[2].ToString()));
            }

            if (Premium[1].ToString() != "" && Premium[1].ToString() != "0.00")
            {
                _etiPdfOut.PremiumGST = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[1].ToString()));
            }

            //_etiPdfOut.PremiumGST = "S$ " + Premium[1].ToString();

            //double _dtotDue = Convert.ToDouble(Premium[0].ToString());
            //_etiPdfOut.TotalDueInvoice = _dtotDue.ToString("#,000.00"); //Double S$ for Tax Invoice
            //_etiPdfOut.TotalDueSchedule = "S$ " + _dtotDue.ToString("#,000.00");

            if (Premium[0].ToString() != "" && Premium[0].ToString() != "0.00")
            {
                _etiPdfOut.TotalDueInvoice = string.Format("{0:0,0.00}", Convert.ToDouble(Premium[0].ToString()));
                _etiPdfOut.TotalDueSchedule = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[0].ToString()));
            }

            if (Premium[16].ToString() != "" && Premium[16].ToString() != "0.00")
            {
                _etiPdfOut.DiscountedPremium = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[16].ToString()));
            }

            if (Premium[17].ToString() != "" && Premium[17].ToString() != "0.00")
            {
                _etiPdfOut.PromoDiscount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[17].ToString()));
            }

            _etiPdfOut.RegistrationNo = _dsPolicy.Tables[0].Rows[0]["VechicleNo"].ToString();

            _etiPdfOut.MakeModel = _dsPolicy.Tables[1].Rows[0]["Description"].ToString();
            _etiPdfOut.TypeOfCover = _cxcovertypeid; // _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            _etiPdfOut.NoOfSeats = "";
            _etiPdfOut.BodyType = _dsPolicy.Tables[1].Rows[0]["Body"].ToString();
            _etiPdfOut.EngineNo = _dsPolicy.Tables[0].Rows[0]["EngineNo"].ToString();
            _etiPdfOut.Capacity = _dsPolicy.Tables[1].Rows[0]["CC"].ToString();
            _etiPdfOut.RegYear = _dsPolicy.Tables[0].Rows[0]["RegYear"].ToString();
            _etiPdfOut.ChassisNo = _dsPolicy.Tables[0].Rows[0]["ChassisNo"].ToString();
            _etiPdfOut.CertificateRef = " ";
            _etiPdfOut.DriverNameList = _cardriverList;
            _etiPdfOut.ExcessNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[8].ToString()));

            //Aug 25 2011 - Cheryl
            //if (_cxcovertypeid.Trim() == "2") //Panel
            //{
            //    double dexcess = Convert.ToDouble(Premium[9].ToString()) * 2;
            //    _etiPdfOut.ExcessUnNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", dexcess);
            //}
            //else
            //{
            _etiPdfOut.ExcessUnNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[9].ToString()));
            //}

            if (_dsPolicy.Tables[2].Rows.Count > 0)
            {
                _etiPdfOut.HirePurchaseCompanyName = _dsPolicy.Tables[2].Rows[0]["HPCompanyName"].ToString();
            }
            else
            {
                _etiPdfOut.HirePurchaseCompanyName = "N/A";
            }
            _etiPdfOut.CertificateNumber = _policynumber.PolicyNumber;

            if (!_etiPdfOut.GenerateAllOuput(out fileList))
            {
                fileList[0] = "";
                fileList[1] = "";
                fileList[2] = "";
            }
        }
        else
        {
            fileList[0] = "";
            fileList[1] = "";
            fileList[2] = "";

        }

        return fileList;
    }

    public string[] GeneratePDFTI(long RefID, string ORDERNO, out string customername, out Int64 policyNumberId, out string policyNumber)
    {
        CE.TravelInsurance.EtiqaTAInsurancePDFOutput _etiPdfOut = new CE.TravelInsurance.EtiqaTAInsurancePDFOutput();
        string[] fileList = new string[3];
        DataSet _dsPolicy = new DataSet();
        ProposalDownloadDB _proposaldownloaddb = new ProposalDownloadDB();
        _dsPolicy = _proposaldownloaddb.GetEtiqaTIPDF(RefID);

        string Premium = _dsPolicy.Tables[0].Rows[0]["TotalPremium"].ToString();

        EtiqaTAPolicyNumber _policynumber = new EtiqaTAPolicyNumber();
        EtiqaTAPolicyNumberDB _policynumberdb = new EtiqaTAPolicyNumberDB();

        _policynumber = _policynumberdb.GetNewPolicyNumber(30);
        policyNumberId = 0;
        policyNumber = "";
        customername = "";


        if (_policynumber != null && _dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _etiPdfOut.ProposalId = RefID.ToString();
            _etiPdfOut.CustomerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            customername = _etiPdfOut.CustomerName;
            _etiPdfOut.CustomerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _etiPdfOut.CustomerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
            _etiPdfOut.PolicyNumber = _policynumber.PolicyNumber;
            policyNumberId = _policynumber.PolicyNumberId;
            policyNumber = _policynumber.PolicyNumber;
            _etiPdfOut.GSTRegNumber = "M90363266Y";
            _etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();
            _etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";

            _etiPdfOut.AcountAgency = "000598/ 000598";
            _etiPdfOut.Agency = "000598";
            _etiPdfOut.Account = "000598";
            _etiPdfOut.CardType = "";
            _etiPdfOut.CardLastFourDigit = "";

            _etiPdfOut.ClassOfPolicy = "VacationShield Plus Insurance";
            _etiPdfOut.IssuedOn = _dsPolicy.Tables[0].Rows[0]["StartDate"].ToString() + " in SINGAPORE";
            _etiPdfOut.ReplacePolicyNumber = "";
            _etiPdfOut.Client = "";
            _etiPdfOut.AcceptanceDate = (Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["StartDate"].ToString())).ToString("dd-MMM-yyyy");
            _etiPdfOut.PeriodInsuranceStart = (Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["StartDate"].ToString())).ToString("dd-MMM-yyyy");
            _etiPdfOut.PeriodInsuranceEnd = (Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["EndDate"].ToString())).ToString("dd-MMM-yyyy");

            int noOfDays = 0;
            noOfDays = ComputeDuration(Convert.ToDateTime(_etiPdfOut.PeriodInsuranceStart), Convert.ToDateTime(_etiPdfOut.PeriodInsuranceEnd));

            _etiPdfOut.NoOfDays = noOfDays.ToString();
            _etiPdfOut.PlanName = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            _etiPdfOut.TypeOfPlan = _dsPolicy.Tables[0].Rows[0]["PlanType"].ToString();
            _etiPdfOut.Destination = _dsPolicy.Tables[0].Rows[0]["Country"].ToString();

            _etiPdfOut.TotalDueInvoice = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString()));
            _etiPdfOut.isPromo = false;
            _etiPdfOut.NewPremium = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString()));

            //check for promo
            if (_dsPolicy.Tables[2].Rows.Count > 0)
            {
                _etiPdfOut.isPromo = true;
                _etiPdfOut.TotalDueInvoice = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString()) * 100 / 90);
                _etiPdfOut.NewPremium = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString()));
            }
           
            _etiPdfOut.NRICNo = _dsPolicy.Tables[0].Rows[0]["NRICNo"].ToString();
            _etiPdfOut.DOB = (Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["DOB"].ToString())).ToString("dd-MMM-yyyy");
            _etiPdfOut.Gender = _dsPolicy.Tables[0].Rows[0]["Gender"].ToString();

            _etiPdfOut.ChildCount = _dsPolicy.Tables[0].Rows[0]["NoOfChilds"].ToString();
            _etiPdfOut.SpouseName = "";
            _etiPdfOut.SpouseNRICNo = "";
            _etiPdfOut.SpouseDOB = "";
            _etiPdfOut.SpouseOccupation = "";

            //if (_etiPdfOut.SpouseStatus == "Yes")
            //{
            //    if (_dsPolicy.Tables[1].Rows.Count > 0)
            //    {
            //        _etiPdfOut.SpouseName = _dsPolicy.Tables[1].Rows[0]["SpouseName"].ToString();
            //        _etiPdfOut.SpouseNRICNo = _dsPolicy.Tables[1].Rows[0]["SpouseNRICNo"].ToString();
            //        _etiPdfOut.SpouseDOB = (Convert.ToDateTime(_dsPolicy.Tables[1].Rows[0]["SPDOB"].ToString())).ToString("dd/MM/yyyy");
            //        _etiPdfOut.SpouseOccupation = _dsPolicy.Tables[1].Rows[0]["SpouseOccupation"].ToString();
            //    }
            //}

            //_etiPdfOut.ChildName1 = "";
            //_etiPdfOut.ChildNRICNo1 = "";
            //_etiPdfOut.ChildDOB1 = "";

            //_etiPdfOut.ChildName2 = "";
            //_etiPdfOut.ChildNRICNo2 = "";
            //_etiPdfOut.ChildDOB2 = "";

            //_etiPdfOut.ChildName3 = "";
            //_etiPdfOut.ChildNRICNo3 = "";
            //_etiPdfOut.ChildDOB3 = "";

            //_etiPdfOut.ChildName4 = "";
            //_etiPdfOut.ChildNRICNo4 = "";
            //_etiPdfOut.ChildDOB4 = "";

            //_etiPdfOut.ChildName5 = "";
            //_etiPdfOut.ChildNRICNo5 = "";
            //_etiPdfOut.ChildDOB5 = "";
            //Int16 _NoofChilds = Convert.ToInt16(_etiPdfOut.ChildCount);
            //if (_dsPolicy.Tables[2].Rows.Count > 0)
            //{
            //    for (int i = 0; i < _NoofChilds; i++)
            //    {
            //        if (i == 0)
            //        {
            //            _etiPdfOut.ChildName1 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
            //            _etiPdfOut.ChildNRICNo1 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
            //            _etiPdfOut.ChildDOB1 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
            //        }
            //        if (i == 1)
            //        {
            //            _etiPdfOut.ChildName2 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
            //            _etiPdfOut.ChildNRICNo2 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
            //            _etiPdfOut.ChildDOB2 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
            //        }
            //        if (i == 2)
            //        {
            //            _etiPdfOut.ChildName3 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
            //            _etiPdfOut.ChildNRICNo3 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
            //            _etiPdfOut.ChildDOB3 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
            //        }
            //        if (i == 3)
            //        {
            //            _etiPdfOut.ChildName4 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
            //            _etiPdfOut.ChildNRICNo4 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
            //            _etiPdfOut.ChildDOB4 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
            //        }
            //        if (i == 4)
            //        {
            //            _etiPdfOut.ChildName5 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
            //            _etiPdfOut.ChildNRICNo5 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
            //            _etiPdfOut.ChildDOB5 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
            //        }
            //    }
            //}
            //Modified on 20 Dec 2010 by Sakthi - End

            if (!_etiPdfOut.GenerateAllOuput(out fileList))
            {
                fileList[0] = "";
                fileList[1] = "";
                fileList[2] = "";
            }
        }
        else
        {
            fileList[0] = "";
            fileList[1] = "";
            fileList[2] = "";
        }

        return fileList;
    }

    private string GetCarDriverNameList(string OrderNumber)
    {
        string _carDriverNameList = "";
        Int64 _UserId = 0;
        Int64 _CarId = 0;

        CarDriverDB _cardriverdb = new CarDriverDB();

        MyDatabase _db = new MyDatabase();

        string strSql = "";
        strSql = @"SELECT CECarId, RefCEUserId FROM tblCarMoreDetail_Etiqa WHERE OrderNo = " + OrderNumber;
        DataTable _dtResult = new DataTable();
        _dtResult = _db.GetDataTable(strSql);

        if (_dtResult != null && _dtResult.Rows.Count > 0)
        {
            _CarId = Convert.ToInt64(_dtResult.Rows[0][0].ToString());
            _UserId = Convert.ToInt64(_dtResult.Rows[0][1].ToString());

            DataTable _driverList = _cardriverdb.GetDriverList(_CarId, _UserId);

            if (_driverList != null && _driverList.Rows.Count > 0)
            {
                for (int i = 0; i < _driverList.Rows.Count; i++)
                {
                    if (_driverList.Rows[i]["SurName"] != null && _driverList.Rows[i]["SurName"].ToString() != string.Empty)
                    {
                        _carDriverNameList += _driverList.Rows[i]["SurName"].ToString();
                    }

                    if (_driverList.Rows[i]["GivenName"] != null && _driverList.Rows[i]["GivenName"].ToString() != string.Empty)
                    {
                        _carDriverNameList += " " + _driverList.Rows[i]["GivenName"].ToString();
                    }

                    if ((i + 1) < _driverList.Rows.Count)
                        _carDriverNameList += ";";
                }
            }
        }
        return _carDriverNameList;
    }

    private void GetUserInformation(Account _account)
    {
        DateTime dtNow = new DateTime();
        dtNow = CE.Global.General.GetUTCDateTime();
        Session[CE.Global.General.UserId] = _account.CEID;
        Session[CE.Global.General.UserTypeId] = _account.UserTypeId;

        if (_account.UserTypeId == Convert.ToInt32(CE.Global.CEUserType.Provider))
        {
            CE.Provider.ProviderCompanyLink _comlink = new CE.Provider.ProviderCompanyLink();
            CE.Provider.ProviderCompanyLinkDB _comlinkdb = new CE.Provider.ProviderCompanyLinkDB();
            _comlink = _comlinkdb.GetProviderCompanyId(_account.CEID);
            if (_comlink != null)
            {
                Session[CE.Global.General.ProviderCompanyId] = _comlink.ProviderCompanyId;
            }
        }

        Session[CE.Global.General.UserEmail] = _account.Email;
        Session[CE.Global.General.StartTime] = dtNow.ToString();
        bool isProfileComplete = false;
        bool isContactComplete = false;

        ProfileDB _profiledb = new ProfileDB();
        Profile _profile = new Profile();
        Contact _contact = new Contact();
        ContactDB _contactdb = new ContactDB();

        _contact = _contactdb.GetContact(_account.CEID);

        _profile = _profiledb.GetProfile(_account.CEID);



        if (_contact != null)
        {
            isContactComplete = true;
            Session[CE.Global.General.UserPhoneNo] = _contact.Mobile;
        }
        else
        {
            Session[CE.Global.General.UserPhoneNo] = "No Phone No";
        }

        if (_profile != null)
        {
            if (_profile.FirstName != null && _profile.FirstName != string.Empty)
            {
                Session[CE.Global.General.UserName] = Server.HtmlDecode(_profile.SurName) + " " + Server.HtmlDecode(_profile.FirstName);
            }
            else
            {
                Session[CE.Global.General.UserName] = Server.HtmlDecode(_profile.SurName);
            }

            if (!string.IsNullOrEmpty(_profile.IDType) &&
                !string.IsNullOrEmpty(_profile.IDNo) &&
                _profile.IDNo.Length == 9)
            {
                Session[CE.Global.General.UserICNumber] = _profile.IDType + " " + _profile.IDNo;
            }

            if (_profile.Status.ToLower() == "complete")
            {
                isProfileComplete = true;
            }
            else
            {
                isProfileComplete = false;
            }
        }
        else
        {
            Session[CE.Global.General.UserName] = "No Name";
        }

        if (isProfileComplete && isContactComplete)
        {
            Session[CE.Global.General.UserProfileAndContactComplete] = "1";
        }
        else
        {
            Session[CE.Global.General.UserProfileAndContactComplete] = "0";
        }

    }

    private string GetFilePath()
    {
        string _filePath = CE.Configuration.MyConfigurationManager.GetAppSettingValue("FileOutputPath").ToString();
        _filePath += "exceloutput\\etiqa\\";
        CE.InputOut.MyIO _myIO = new CE.InputOut.MyIO();
        string _todaydate = CE.Global.General.GetUTCDateTime().ToString("yyyyMMMdd");
        if (!_myIO.CheckDirectory(_filePath + _todaydate))
        {
            if (_myIO.CreateDirectory(_filePath, _todaydate))
            {
                _filePath += _todaydate + "\\";
            }
        }
        else
        {
            _filePath += _todaydate + "\\";
        }

        return _filePath;
    }

    public Int64 InsertEtiqaPaymentGateWayReply(string _ordernumber, string _authresp, string _authcode, string _authdt, string _cctref, string _amount, string _cemail)
    {
        MyDatabase _db = new MyDatabase();
        Int64 _result = -1;
        string _spName = "sprocEtiqPaymentGateWayReplyInsertSingleItem";

        SortedList _sPara = new SortedList();

        #region Parameter

        _sPara.Add("@OrderNo", _ordernumber);
        _sPara.Add("@AuthResp", _authresp);
        _sPara.Add("@Info1", _authcode);
        _sPara.Add("@Info2", _authdt);
        _sPara.Add("@Info3", _cctref);
        _sPara.Add("@Info4", _amount);
        _sPara.Add("@Info5", _cemail);
        #endregion

        _result = _db.ExecuteSP(_spName, _sPara);

        return _result;
    }

    public string CheckProduct(string _ordernumber)
    {
        string _product = "";
        DataTable _dtResult = new DataTable("product");

        MyDatabase _db = new MyDatabase();
        string _spName = "sprocProviderEtiqaPaymentSelectProduct";

        SortedList _sPara = new SortedList();

        #region Parameter

        _sPara.Add("@OrderNo", _ordernumber);
        #endregion

        _dtResult = _db.GetDataTableBySP(_spName, _sPara);

        if (_dtResult.Rows.Count > 0)
        {
            _product = _dtResult.Rows[0]["Product"].ToString();
        }

        return _product;
    }

    protected string GenerateProposalFormToEtiqa(Int64 RefID, string FilePath, string FileName)
    {
        string _returnFilePath = "";
        //string _filePath = FilePath;
        Label lblContent = new Label();
        //string _fileName = FileName;
        string _applicationdate = " : " + CE.Global.General.GetUTCDateTime().ToString("dd/MMM/yyyy") + " " + CE.Global.General.GetUTCDateTime().ToShortTimeString();
        //string convertno = General.GetRefNumber(RefID);
        //_fileName = "E" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;

        StringBuilder strText = new StringBuilder("");
        strText.Append("<table><tr><td colspan='2'><h4>Car Insurance - Proposal Form</h4></td><td colspan='2' align='right'><b>Application Date</b> " + _applicationdate.ToString() + "</td></tr></table>");

        DataSet _dsProposal = new DataSet();
        ProposalDownloadDB _proposaldownloaddb = new ProposalDownloadDB();
        _dsProposal = _proposaldownloaddb.GetEtiqaProposal(RefID, "CI");
        string _html = "";
        try
        {
            if (_dsProposal != null && _dsProposal.Tables[0].Rows.Count > 0)
            {
                #region Variables

                string _name, _unitno, _homeno, _address, _postalcode, _dob, _dd, _mm, _yy, _nationality, _nric, _maritalstatus, _mobile, _occupation;
                string _gender, _expdriving, _vehicleno, _make, _model, _vehicletype, _engineno, _chassisno, _cc, _regyear, _hpstatus, _hpcomp;
                string _insstartdate, _insenddate, _isdd, _ismm, _isyy, _iedd, _iemm, _ieyy, _insstatus, _inscomid, _inscomp, _inspolicyno;
                string _inspldate, _indd, _inmm, _inyy;
                string _drname1, _drdob1, _drdd1, _drmm1, _dryy1, _drocc1, _drexp1, _drname2, _drdob2, _drdd2, _drmm2, _dryy2, _drocc2, _drexp2;
                string _drname3, _drdob3, _drdd3, _drmm3, _dryy3, _drocc3, _drexp3, _drname4, _drdob4, _drdd4, _drmm4, _dryy4, _drocc4, _drexp4;
                string _drname5, _drdob5, _drdd5, _drmm5, _dryy5, _drocc5, _drexp5, _demeritstatus, _demeritcount, _dmdrname1, _dmdrpoint1;
                string _dmdrname2, _dmdrpoint2, _dmdrname3, _dmdrpoint3, _dmdrname4, _dmdrpoint4, _dmdrname5, _dmdrpoint5, _dmdrname6, _dmdrpoint6;
                string _adddriver, _drcount, _claimstatus, _claim1ststat, _claim1stend;
                string claimamt1, _claim2ndstat, _claim2ndend, claimamt2, _claim3rdstat, _claim3rdend, claimamt3, _ncdstatus, _ncdpercent;
                string _inscancelstatus, _cancelreason, _impairstatus, _impairreason, _planname, _annualpremium;
                string _excess, _email, _natureofbusi, _cxaccountno, _aucode, _policyno, _orderno, _nextNCD, _isOFD, _isNCDProtector;
                DateTime _dofb, _isdate, _iedate, _indate, _drdofb1, _drdofb2, _drdofb3, _drdofb4, _drdofb5;
                string _20percentpromo = "";

                _inscancelstatus = ""; _cancelreason = ""; _impairstatus = ""; _impairreason = ""; _demeritstatus = ""; _demeritcount = "";
                _dmdrname1 = ""; _dmdrpoint1 = ""; _dmdrname2 = ""; _dmdrpoint2 = ""; _dmdrname3 = ""; _dmdrpoint3 = ""; _dmdrname4 = "";
                _dmdrpoint4 = ""; _dmdrname5 = ""; _dmdrpoint5 = ""; _dmdrname6 = ""; _dmdrpoint6 = ""; _vehicletype = ""; _expdriving = "";
                _drname1 = ""; _drdob1 = ""; _drdd1 = ""; _drmm1 = ""; _dryy1 = ""; _drocc1 = ""; _drexp1 = "";
                _drname2 = ""; _drdob2 = ""; _drdd2 = ""; _drmm2 = ""; _dryy2 = ""; _drocc2 = ""; _drexp2 = "";
                _drname3 = ""; _drdob3 = ""; _drdd3 = ""; _drmm3 = ""; _dryy3 = ""; _drocc3 = ""; _drexp3 = "";
                _drname4 = ""; _drdob4 = ""; _drdd4 = ""; _drmm4 = ""; _dryy4 = ""; _drocc4 = ""; _drexp4 = "";
                _drname5 = ""; _drdob5 = ""; _drdd5 = ""; _drmm5 = ""; _dryy5 = ""; _drocc5 = ""; _drexp5 = ""; _planname = ""; _annualpremium = "";
                _adddriver = ""; _drcount = "";

                #endregion

                #region Assign Values

                _name = _dsProposal.Tables[0].Rows[0]["Name"].ToString();
                _dofb = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["DOB"].ToString());
                _dd = _dofb.Day.ToString("00");
                _mm = _dofb.Month.ToString("00");
                _yy = _dofb.Year.ToString();
                //_dob = _dd + "/" + _mm + "/" + _yy;
                _dob = _dsProposal.Tables[0].Rows[0]["DOB"].ToString();
                _nationality = _dsProposal.Tables[0].Rows[0]["Nationality"].ToString();
                _unitno = _dsProposal.Tables[0].Rows[0]["UnitNo"].ToString();
                _homeno = _dsProposal.Tables[0].Rows[0]["HomeNo"].ToString();
                _address = _dsProposal.Tables[0].Rows[0]["Address"].ToString();
                _postalcode = _dsProposal.Tables[0].Rows[0]["PostalCode"].ToString();
                _nric = _dsProposal.Tables[0].Rows[0]["NRICNo"].ToString();
                _maritalstatus = _dsProposal.Tables[0].Rows[0]["Marital"].ToString();
                _gender = _dsProposal.Tables[0].Rows[0]["Gender"].ToString();
                _email = _dsProposal.Tables[0].Rows[0]["Email"].ToString();

                _natureofbusi = _dsProposal.Tables[0].Rows[0]["NofBusi"].ToString();
                _nextNCD = _dsProposal.Tables[0].Rows[0]["NCDNextPercent"].ToString();
                _isNCDProtector = _dsProposal.Tables[0].Rows[0]["IsNCDProtector"].ToString(); ;
                _isOFD = _dsProposal.Tables[0].Rows[0]["IsOffDriveCom"].ToString();

                _planname = _dsProposal.Tables[0].Rows[0]["PlanName"].ToString();
                _annualpremium = _dsProposal.Tables[0].Rows[0]["AnnualPremium"].ToString();

                _expdriving = _dsProposal.Tables[0].Rows[0]["YearDriveSg"].ToString();
                _occupation = _dsProposal.Tables[0].Rows[0]["Description"].ToString();
                _mobile = _dsProposal.Tables[0].Rows[0]["ContactNo"].ToString();

                _excess = _dsProposal.Tables[0].Rows[0]["Excess"].ToString();

                if (_dsProposal.Tables[7].Rows.Count > 0)
                {
                    _20percentpromo = _dsProposal.Tables[7].Rows[0]["info1"].ToString();
                }


                //if (_planname.Trim() == "Panel Workshops")
                //{
                //    double _dexcess = (double.Parse(_excess)) / 2;
                //    _excess = _dexcess.ToString();
                //}


                if (_dsProposal.Tables[0].Rows[0]["IsClaimLastThreeYear"].ToString() == "False")
                {
                    _claimstatus = "No";
                }
                else
                {
                    _claimstatus = "Yes";
                }
                if (_dsProposal.Tables[0].Rows[0]["HasPreInsurer"].ToString() == "True")
                {
                    _insstatus = "Yes";
                    if (_dsProposal.Tables[2].Rows.Count > 0)
                    {
                        _inscomp = _dsProposal.Tables[2].Rows[0]["InsCompanyName"].ToString();
                        _inspolicyno = _dsProposal.Tables[0].Rows[0]["PreInsPolicyNo"].ToString();
                        _indate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["PreInsExpiryDate"].ToString());
                        _indd = _indate.Day.ToString("00");
                        _inmm = _indate.Month.ToString("00");
                        _inyy = _indate.Year.ToString();
                        _inspldate = _indd + "/" + _inmm + "/" + _inyy;
                        //_inspldate = _dsProposal.Tables[0].Rows[0]["PreInsExpiryDate"].ToString();
                        _inscomid = _dsProposal.Tables[0].Rows[0]["PreInsurerName"].ToString();
                    }
                    else
                    {
                        _insstatus = "No";
                        _inscomp = "Not Applicable";
                        _inspolicyno = "";
                        _inspldate = "";
                        _inscomid = "01";
                    }
                }
                else
                {
                    _insstatus = "No";
                    _inscomp = "Not Applicable";
                    _inspolicyno = "";
                    _inspldate = "";
                    _inscomid = "01";
                }


                _cxaccountno = _dsProposal.Tables[0].Rows[0]["CXAccountNo"].ToString();
                _policyno = _dsProposal.Tables[0].Rows[0]["PolicyNumber"].ToString();
                _aucode = _dsProposal.Tables[0].Rows[0]["AuCode"].ToString();
                _orderno = _dsProposal.Tables[0].Rows[0]["OrderNo"].ToString();


                _ncdpercent = _dsProposal.Tables[0].Rows[0]["NCDPercent"].ToString();
                if (_ncdpercent.ToString() == "-1")
                {
                    _ncdpercent = "No NCD(First Policy)";
                }
                else
                {
                    _ncdpercent += "%";
                }

                if (_dsProposal.Tables[1].Rows.Count > 0)
                {
                    _claim1ststat = _dsProposal.Tables[1].Rows[0]["Claim1stStartDate"].ToString();
                    _claim1stend = _dsProposal.Tables[1].Rows[0]["Claim1stEndDate"].ToString();
                    claimamt1 = _dsProposal.Tables[1].Rows[0]["NOCLast1Value"].ToString();

                    _claim2ndstat = _dsProposal.Tables[1].Rows[0]["Claim2ndStartDate"].ToString();
                    _claim2ndend = _dsProposal.Tables[1].Rows[0]["Claim2ndEndDate"].ToString();
                    claimamt2 = _dsProposal.Tables[1].Rows[0]["NOCLast2Value"].ToString();

                    _claim3rdstat = _dsProposal.Tables[1].Rows[0]["Claim3rdStartDate"].ToString();
                    _claim3rdend = _dsProposal.Tables[1].Rows[0]["Claim3rdEndDate"].ToString();
                    claimamt3 = _dsProposal.Tables[1].Rows[0]["NOCLast3Value"].ToString();
                }
                else
                {
                    _claim1ststat = "";
                    _claim1stend = "";
                    claimamt1 = "";

                    _claim2ndstat = "";
                    _claim2ndend = "";
                    claimamt2 = "";

                    _claim3rdstat = "";
                    _claim3rdend = "";
                    claimamt3 = "";
                    _claimstatus = "No";
                }
                if (_dsProposal.Tables[0].Rows[0]["HasInsuranceTerminated"].ToString() == "True")
                {
                    _inscancelstatus = "Yes";
                    _cancelreason = _dsProposal.Tables[0].Rows[0]["InsuranceTerminatedReason"].ToString();
                }
                else
                {
                    _inscancelstatus = "No";
                    _cancelreason = "";
                }

                if (_dsProposal.Tables[0].Rows[0]["HasPhysicalImpairment"].ToString() == "True")
                {
                    _impairstatus = "Yes";
                    _impairreason = _dsProposal.Tables[0].Rows[0]["PhysicalImpairmentReason"].ToString();
                }
                else
                {
                    _impairstatus = "No";
                    _impairreason = "";
                }

                if (_dsProposal.Tables[0].Rows[0]["HasDemeritPoints"].ToString() == "True")
                {
                    if (_dsProposal.Tables[6].Rows.Count > 0)
                    {
                        _demeritstatus = "Yes";
                        _demeritcount = _dsProposal.Tables[6].Rows.Count.ToString();

                        _dmdrname1 = _dsProposal.Tables[6].Rows[0]["DriverName"].ToString();
                        _dmdrpoint1 = _dsProposal.Tables[6].Rows[0]["TotalDemeritPoints"].ToString();
                        if (_dsProposal.Tables[6].Rows.Count > 1)
                        {
                            _dmdrname2 = _dsProposal.Tables[6].Rows[1]["DriverName"].ToString();
                            _dmdrpoint2 = _dsProposal.Tables[6].Rows[1]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 2)
                        {
                            _dmdrname3 = _dsProposal.Tables[6].Rows[2]["DriverName"].ToString();
                            _dmdrpoint3 = _dsProposal.Tables[6].Rows[2]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 3)
                        {
                            _dmdrname4 = _dsProposal.Tables[6].Rows[3]["DriverName"].ToString();
                            _dmdrpoint4 = _dsProposal.Tables[6].Rows[3]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 4)
                        {
                            _dmdrname5 = _dsProposal.Tables[6].Rows[4]["DriverName"].ToString();
                            _dmdrpoint5 = _dsProposal.Tables[6].Rows[4]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 5)
                        {
                            _dmdrname6 = _dsProposal.Tables[6].Rows[5]["DriverName"].ToString();
                            _dmdrpoint6 = _dsProposal.Tables[6].Rows[5]["TotalDemeritPoints"].ToString();
                        }
                    }
                    else
                    {
                        _demeritstatus = "No";
                        _demeritcount = "0";
                    }
                }
                else
                {
                    _demeritstatus = "No";
                    _demeritcount = "0";
                }

                _vehicleno = _dsProposal.Tables[0].Rows[0]["VechicleNo"].ToString();
                _regyear = _dsProposal.Tables[0].Rows[0]["RegYear"].ToString();
                _model = _dsProposal.Tables[5].Rows[0]["Description"].ToString();
                _make = _dsProposal.Tables[5].Rows[0]["Make"].ToString(); ;
                _cc = _dsProposal.Tables[5].Rows[0]["CC"].ToString();
                _vehicletype = _dsProposal.Tables[5].Rows[0]["Body"].ToString();
                _engineno = _dsProposal.Tables[0].Rows[0]["EngineNo"].ToString();
                _chassisno = _dsProposal.Tables[0].Rows[0]["ChassisNo"].ToString();

                _isdate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["StartDate"].ToString());
                _isdd = _isdate.Day.ToString("00");
                _ismm = _isdate.Month.ToString("00");
                _isyy = _isdate.Year.ToString();
                _insstartdate = _isdd + "/" + _ismm + "/" + _isyy;
                //_insstartdate = _dsProposal.Tables[0].Rows[0]["StartDate"].ToString();

                _iedate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["EndDate"].ToString());
                _iedd = _iedate.Day.ToString("00");
                _iemm = _iedate.Month.ToString("00");
                _ieyy = _iedate.Year.ToString();
                _insenddate = _iedd + "/" + _iemm + "/" + _ieyy;
                //_insenddate = _dsProposal.Tables[0].Rows[0]["EndDate"].ToString();

                if (_dsProposal.Tables[3].Rows.Count > 0)
                {
                    _hpstatus = "Yes";
                    _hpcomp = _dsProposal.Tables[3].Rows[0]["HPCompanyName"].ToString();

                    if (_hpcomp.ToString() == "")
                    {
                        _hpcomp = "Not Applicable";
                    }
                }
                else
                {
                    _hpstatus = "No";
                    _hpcomp = "Not Applicable";
                }
                if (_dsProposal.Tables[0].Rows[0]["AddDriver"].ToString() == "Yes")
                {
                    _adddriver = "Yes";
                    _drcount = _dsProposal.Tables[0].Rows[0]["NoOfDriver"].ToString();
                }
                else
                {
                    _adddriver = "No";
                    _drcount = "0";
                }
                if (_dsProposal.Tables[4].Rows.Count > 0)
                {
                    if (Convert.ToInt16(_drcount) > 0)
                    {
                        _drname1 = _dsProposal.Tables[4].Rows[0]["DriverName"].ToString();
                        _drdofb1 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[0]["DOB"].ToString());
                        _drdd1 = _drdofb1.Day.ToString("00");
                        _drmm1 = _drdofb1.Month.ToString("00");
                        _dryy1 = _drdofb1.Year.ToString();
                        //_drdob1 = _drdd1 + "/" + _drmm1 + "/" + _dryy1;
                        _drdob1 = _dsProposal.Tables[4].Rows[0]["DOB"].ToString();
                        _drocc1 = _dsProposal.Tables[4].Rows[0]["Description"].ToString();
                        _drexp1 = _dsProposal.Tables[4].Rows[0]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 1)
                    {
                        _drname2 = _dsProposal.Tables[4].Rows[1]["DriverName"].ToString();
                        _drdofb2 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[1]["DOB"].ToString());
                        _drdd2 = _drdofb2.Day.ToString("00");
                        _drmm2 = _drdofb2.Month.ToString("00");
                        _dryy2 = _drdofb2.Year.ToString();
                        //_drdob2 = _drdd2 + "/" + _drmm2 + "/" + _dryy2;
                        _drdob2 = _dsProposal.Tables[4].Rows[1]["DOB"].ToString();
                        _drocc2 = _dsProposal.Tables[4].Rows[1]["Description"].ToString();
                        _drexp2 = _dsProposal.Tables[4].Rows[1]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 2)
                    {
                        _drname3 = _dsProposal.Tables[4].Rows[2]["DriverName"].ToString();
                        _drdofb3 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[2]["DOB"].ToString());
                        _drdd3 = _drdofb3.Day.ToString("00");
                        _drmm3 = _drdofb3.Month.ToString("00");
                        _dryy3 = _drdofb3.Year.ToString();
                        //_drdob3 = _drdd3 + "/" + _drmm3 + "/" + _dryy3;
                        _drdob3 = _dsProposal.Tables[4].Rows[2]["DOB"].ToString();
                        _drocc3 = _dsProposal.Tables[4].Rows[2]["Description"].ToString();
                        _drexp3 = _dsProposal.Tables[4].Rows[2]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 3)
                    {
                        _drname4 = _dsProposal.Tables[4].Rows[3]["DriverName"].ToString();
                        _drdofb4 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[3]["DOB"].ToString());
                        _drdd4 = _drdofb4.Day.ToString("00");
                        _drmm4 = _drdofb4.Month.ToString("00");
                        _dryy4 = _drdofb4.Year.ToString();
                        //_drdob4 = _drdd4 + "/" + _drmm4 + "/" + _dryy4;
                        _drdob4 = _dsProposal.Tables[4].Rows[3]["DOB"].ToString();
                        _drocc4 = _dsProposal.Tables[4].Rows[3]["Description"].ToString();
                        _drexp4 = _dsProposal.Tables[4].Rows[3]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 4)
                    {
                        _drname5 = _dsProposal.Tables[4].Rows[4]["DriverName"].ToString();
                        _drdofb5 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[4]["DOB"].ToString());
                        _drdd5 = _drdofb5.Day.ToString("00");
                        _drmm5 = _drdofb5.Month.ToString("00");
                        _dryy5 = _drdofb5.Year.ToString();
                        //_drdob5 = _drdd5 + "/" + _drmm5 + "/" + _dryy5;
                        _drdob5 = _dsProposal.Tables[4].Rows[4]["DOB"].ToString();
                        _drocc5 = _dsProposal.Tables[4].Rows[4]["Description"].ToString();
                        _drexp5 = _dsProposal.Tables[4].Rows[4]["YearOfDrivingInSg"].ToString();
                    }
                }

                #endregion

                #region Excel Generation

                //_html = "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + "</td><td colspan='2' align='right'><b>Annual Premium</b> : $" + _annualpremium.ToString() + "</td></tr></table>";
                //_html += "<div><div><b>Details of Proposer</b></div><div style='text-align: center'>"

                _html = "";

                if (_20percentpromo != "" && _20percentpromo == "20discount")
                {
                    _html += "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + "</td><td colspan='2' align='right'><b>Annual Premium Before Discount</b> : $" + String.Format("{0:0,0.00}", Convert.ToSingle(_annualpremium) * 100 / 80) + "</td></tr>";
                    _html += "<tr><td colspan='2'></td><td colspan='2' align='right'><b>Annual Premium After Discount</b> : $" + _annualpremium.ToString() + "</td></tr>";
                }
                else
                {
                    _html += "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + "</td><td colspan='2' align='right'><b>Annual Premium</b> : $" + _annualpremium.ToString() + "</td></tr>";
                }

                _html += "<tr><td colspan='2'><b>Details of Proposer</b></td><td colspan='2' align='right'><b>Excess Amount</b> : $" + _excess.ToString() + "</td></tr>";

                //12dec2011 Cheryl Lanot - etiqa car 20% promo
                if (_20percentpromo != "" && _20percentpromo == "20discount")
                {
                    _html += "<tr><td colspan='2'></td><td colspan='2' align='right'><b>20% FESTIVE PROMO discount</b> : $" + String.Format("{0:0,0.00}", Convert.ToSingle(_annualpremium) * 100 / 80 * 0.20) + "</td></tr>";
                }
                _html += "</table>";
                _html += "<div><div style='text-align: center'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"
                         + "<tr>"
                         + "<td width='50%'>" //Start 1
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Name</td>"
                         + "<td width='50%' align='left'>" + _name.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td align='left'>Address</td>"
                         + "<td align='left'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0'>"
                         + "<tr><td align='left'>#" + _unitno.ToString() + " - " + _homeno.ToString() + "</td></tr>"
                         + "<tr><td align='left'>" + _address.ToString() + "</td></tr>"
                         + "<tr><td align='left'>Postal Code " + _postalcode.ToString() + "</td></tr>"
                         + "</table>"
                         + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Email</td>"
                         + "<td width='50%' align='left'>" + _email.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Occupation / Business</td>"
                         + "<td width='50%' align='left'>" + _occupation.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Contact No</td>"
                         + "<td width='50%' align='left'>" + _mobile.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Existing NCD</td>"
                         + "<td width='50%' align='left'>" + _ncdpercent.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>OFD</td>"
                         + "<td width='50%' align='left'>" + _isOFD.ToString() + "</td>"
                         + "</tr>"

                         + "</table>"
                         + "</td>" //End 1

                         + "<td width='50%'>" //Start 2
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Date of Birth</td>"
                         + "<td width='50%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _dob.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Nationality</td>"
                         + "<td width='50%' align='left'>" + _nationality.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>NRIC Number</td>"
                         + "<td width='50%' align='left'>" + _nric.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Sex</td>"
                         + "<td width='50%' align='left'>" + _gender.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Marital Status</td>"
                         + "<td width='50%' align='left'>" + _maritalstatus.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Nature of Business</td>"
                         + "<td width='50%' align='left'>" + _natureofbusi.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Driving Experience</td>"
                         + "<td width='50%' align='left'>" + _expdriving.ToString() + "</td>"
                         + "</tr>"

                          + "<tr>"
                         + "<td width='50%' align='left'>NCD upon renewal</td>"
                         + "<td width='50%' align='left'>" + _nextNCD.ToString() + "</td>"
                         + "</tr>"

                          + "<tr>"
                         + "<td width='50%' align='left'>Is NCDProtector</td>"
                         + "<td width='50%' align='left'>" + _isNCDProtector.ToString() + "</td>"
                         + "</tr>"

                         + "</table>"
                         + "</td>" //End 2
                         + "</tr>"

                         + "<tr>"
                         + "<td colspan='2'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr><td colspan='4' align='left'><b>Particulars of Vehicle</b></td></tr>"

                         + "<tr><td style='width:25%' align='left'>Registration No</td>"
                         + "<td style='width: 25%' align='left'>" + _vehicleno.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Year of Registration</td>"
                         + "<td style='width: 25%' align='left'>" + _regyear.ToString() + "</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Make of Vehicle</td>"
                         + "<td style='width: 25%' align='left'>" + _make.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Vehicle Model</td>"
                         + "<td style='width: 25%' align='left'>" + _model.ToString() + "</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Vehicle Type</td>"
                         + "<td style='width: 25%' align='left'>" + _vehicletype.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Engine capacity</td>"
                         + "<td style='width: 25%' align='left'>" + _cc.ToString() + "cc</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Engine No</td>"
                         + "<td style='width: 25%' align='left'>" + _engineno.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Chassis No</td>"
                         + "<td style='width: 25%' align='left'>" + _chassisno.ToString() + "</td></tr>"

                         + "<tr><td colspan='4' align='left'><b>Additional drivers : </b>" + _adddriver.ToString() + "</td></tr>";

                if (Convert.ToInt16(_drcount) > 0)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 1</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob1.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp1.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 1)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 2</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob2.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp2.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 2)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 3</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob3.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp3.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 3)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 4</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob4.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp4.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 4)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 5</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob5.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp5.ToString() + "</td></tr>";
                }

                _html += "<tr><td colspan='4' align='left'>Period of insurance</td></tr>"

                + "<tr><td style='width:25%' align='left'>From</td>"
                + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _insstartdate.ToString() + "</td>"
                + "<td style='width: 25%' align='left'>To</td>"
                + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _insenddate.ToString() + "</td></tr>"

                + "<tr><td style='width:25%' align='left'>Is this vehicle under Hire Purchase?</td>"
                + "<td style='width: 25%' align='left'>" + _hpstatus.ToString() + "</td>"
                + "<td style='width: 25%' align='left'>Name of finance Company</td>"
                + "<td style='width: 25%' align='left'>" + _hpcomp.ToString() + "</td></tr>";

                if (_insstatus == "Yes" && _inscomid != "01" && _inscomid != "177")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Current Insurance Company</td>"
                            + "<td colspan='3' align='left'>" + _inscomp.ToString() + "</td></tr>"

                            + "<tr><td style='width:25%' align='left'>Policy No.</td>"
                            + "<td style='width:25%' align='left'>" + _inspolicyno.ToString() + "</td>"
                            + "<td style='width: 25%' align='left'>Expiry Date</td>"
                            + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _inspldate.ToString() + "</td></tr>";
                }
                else
                {
                    _html += "<tr><td style='width: 25%' align='left'>Current Insurance Company</td>"
                            + "<td colspan='3' align='left'>" + _inscomp.ToString() + "</td></tr>";
                }
                _html += "<tr>"
                + "<td colspan='4' align='left'>Claim history o proposer & named / Authorised Driver(s)(Last 3 years) : " + _claimstatus.ToString() + "</td>"
                + "</tr>";
                if (_claimstatus.ToString() == "Yes")
                {
                    if (claimamt1.ToString() != "0" && claimamt1.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width:25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'> " + _claim1ststat.ToString() + " to " + _claim1stend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt1.ToString() + "</td></tr>";
                    }
                    if (claimamt2.ToString() != "0" && claimamt2.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _claim2ndstat.ToString() + " to " + _claim2ndend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt2.ToString() + "</td></tr>";
                    }
                    if (claimamt3.ToString() != "0" && claimamt3.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _claim3rdstat.ToString() + " to " + _claim3rdend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt3.ToString() + "</td></tr>";
                    }
                }
                _html += "<tr><td colspan='4' align='left'><b>General Questions</td></tr>"

                + "<tr><td colspan='3' align='left'>Have you or your named driver(s) been given / accumulated demerit points during the last 24 months?</td>"
                + "<td style='width: 25%' align='left'>" + _demeritstatus.ToString() + "</td></tr>";
                if (Convert.ToInt16(_demeritcount) > 0)
                {
                    _html += "<tr><td style='width:25%' align='left'>1. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint1.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 1)
                {
                    _html += "<tr><td style='width:25%' align='left'>2. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint2.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 2)
                {
                    _html += "<tr><td style='width:25%' align='left'>3. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint3.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 3)
                {
                    _html += "<tr><td style='width:25%' align='left'>4. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint4.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 4)
                {
                    _html += "<tr><td style='width:25%' align='left'>5. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint5.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 5)
                {
                    _html += "<tr><td style='width:25%' align='left'>5. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname6.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint6.ToString() + "</td></tr>";
                }
                _html += "<tr><td colspan='3' align='left'>Have you or your named driver(s) had any motor insurance proposal declined, cancelled or renewal rejected by any insurance company?</td>"
                + "<td style='width: 25%' align='left'>" + _inscancelstatus.ToString() + "</td></tr>";
                if (_inscancelstatus.ToString() == "Yes")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Reason</td>"
                    + "<td colspan='3' align='left'>" + _cancelreason.ToString() + "</td></tr>";
                }
                _html += "<tr><td colspan='3' align='left'>Have you or your named driver(s) suffered any disease or infirmity that could impair the ability to drive?</td>"
                        + "<td style='width: 25%' align='left'>" + _impairstatus.ToString() + "</td></tr>";
                if (_inscancelstatus.ToString() == "Yes")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Reason</td>"
                    + "<td colspan='3' align='left'>" + _impairreason.ToString() + "</td></tr>";
                }

                _html += "<tr><td colspan='3' align='left'>Has your vehicle been modified/altered from the original manufacturer's specifications, including any LTA approved modification? </td>"
                      + "<td style='width: 25%' align='left'>No</td></tr>";

                _html += "<tr><td colspan='4' align='left'></td></tr>";
                _html += "<tr><td>Account No.</td><td>[" + _cxaccountno + "]</td><td>Policy No.</td><td>[" + _policyno + "]</td></tr>";
                _html += "<tr><td>Order No.</td><td>[" + _orderno + "]</td><td>AuCode</td><td>[" + _aucode + "]</td></tr>";

                _html += "</table>"
                + "</td>"
                + "</tr>"

                + "</table>"
                + "</div></div>";

                #endregion

                lblContent.Text = _html;
                MyIO myio = new MyIO();

                string[] filecontent = new string[1];
                filecontent[0] = strText + lblContent.Text.ToString();

                //_filePath = GetFilePath();
                myio.WriteNewFile(FilePath, FileName + ".xls", filecontent);
                _returnFilePath = FilePath + FileName + ".xls";
                ////SEND MAIL

            }
        }
        catch (Exception Ex)
        {
            Ex = null;
        }

        return _returnFilePath;

    }

    private DataTable getPromoDetails(string ORDERNO)
    {
        DataTable dtPromo = new DataTable();
        PASelectItemPromoDB papromodb = new PASelectItemPromoDB();

        dtPromo = papromodb.GetSelectItemPromo(ORDERNO);

        return dtPromo;
    }

    private DataTable getTIPromoDetails(string ORDERNO)
    {
        DataTable dtPromo = new DataTable();
        PASelectItemPromoDB papromodb = new PASelectItemPromoDB();

        dtPromo = papromodb.GetSelectItemPromo(ORDERNO);

        return dtPromo;
    }

    protected string GenerateTIExcel(Int64 RefID, string FilePath, string FileName, string _orderno, Int64 _newPolicyNumberId, string _newPolicyNumber)
    {
        string _returnFilePath = "";

        Label lblContent = new Label();
        //string _fileName = FileName;
        string _applicationdate = " : " + CE.Global.General.GetUTCDateTime().ToString("dd/MMM/yyyy") + " " + CE.Global.General.GetUTCDateTime().ToShortTimeString();
        //_customername = "";
        //_newPolicyNumberId = 0;
        //_newPolicyNumber = "";
        try
        {
            StringBuilder strText = new StringBuilder("");
            strText.Append("<table><tr><td colspan='2'><h4>Etiqa Travel Insurance - Proposal Form</h4></td><td colspan='2' align='right'><b>Application Date</b> " + _applicationdate.ToString() + "</td></tr></table>");

            DataSet _dsProposal = new DataSet();
            ProposalDownloadDB _proposaldownloaddb = new ProposalDownloadDB();
            _dsProposal = _proposaldownloaddb.GetEtiqaProposal(RefID, "TI");
            string _html = "";

            if (_dsProposal.Tables[0].Rows.Count > 0)
            {
                //EtiqaTAPolicyNumberDB poldb = new EtiqaTAPolicyNumberDB();
                //EtiqaTAPolicyNumber podnum = new EtiqaTAPolicyNumber();
                //podnum = poldb.GetNewPolicyNumber(30);
                //_newPolicyNumberId = podnum.PolicyNumberId;
                //_newPolicyNumber = podnum.PolicyNumber;


                string _name, _dob, _nationality, _nric, _mobile, _hoemphone, _officephone, _faxno, _unitno, _homeno, _address;
                string _postalcode, _email, _dd, _mm, _yy;
                string _plantype, _triptype, _countryarea, _nooftravellers, _totalpremium;
                string _planname, _sex;
                DateTime _dofb = CE.Global.General.GetUTCDateTime(), _startdate, _enddate;
                string quoteref = General.GetRefNumber(RefID);

                _name = ""; _dob = ""; _nationality = ""; _nric = ""; _mobile = ""; _hoemphone = ""; _officephone = "";
                _faxno = ""; _unitno = ""; _homeno = ""; _address = ""; _postalcode = ""; _email = "";
                _dd = ""; _mm = ""; _yy = ""; _plantype = ""; _triptype = ""; _countryarea = ""; _nooftravellers = ""; _totalpremium = "";
                _planname = ""; _sex = "";

                string _insname, _insdob, _insnric, _insgender, _insoccupation, _insnation, _insrelation;
                string _sponame, _spodob, _sponric, _spogender, _spooccupation, _sponation, _sporelation;
                string _childname1, _childdob1, _childnric1, _childgender1, _childnation1, _childrelation1;
                string _childname2, _childdob2, _childnric2, _childgender2, _childnation2, _childrelation2;
                string _childname3, _childdob3, _childnric3, _childgender3, _childnation3, _childrelation3;
                string _childname4, _childdob4, _childnric4, _childgender4, _childnation4, _childrelation4;
                string _childname5, _childdob5, _childnric5, _childgender5, _childnation5, _childrelation5;
                string _childname6, _childdob6, _childnric6, _childgender6, _childnation6, _childrelation6;
                string _noofadults, _noofchilds;
                bool _havepromo = false;

                _insname = ""; _insdob = ""; _insnric = ""; _insgender = ""; _insoccupation = ""; _insnation = ""; _insrelation = "";
                _sponame = ""; _spodob = ""; _sponric = ""; _spogender = ""; _spooccupation = ""; _sponation = ""; _sporelation = "";
                _childname1 = ""; _childdob1 = ""; _childnric1 = ""; _childgender1 = ""; _childnation1 = ""; _childrelation1 = "";
                _childname2 = ""; _childdob2 = ""; _childnric2 = ""; _childgender2 = ""; _childnation2 = ""; _childrelation2 = "";
                _childname3 = ""; _childdob3 = ""; _childnric3 = ""; _childgender3 = ""; _childnation3 = ""; _childrelation3 = "";
                _childname4 = ""; _childdob4 = ""; _childnric4 = ""; _childgender4 = ""; _childnation4 = ""; _childrelation4 = "";
                _childname5 = ""; _childdob5 = ""; _childnric5 = ""; _childgender5 = ""; _childnation5 = ""; _childrelation5 = "";
                _childname6 = ""; _childdob6 = ""; _childnric6 = ""; _childgender6 = ""; _childnation6 = ""; _childrelation6 = "";
                _noofadults = "0"; _noofchilds = "0";


                _name = _dsProposal.Tables[0].Rows[0]["Name"].ToString();
                //_customername = _name;

                if (_dsProposal.Tables[0].Rows[0]["DOB"].ToString() != "")
                {
                    _dofb = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["DOB"].ToString());

                    _dd = _dofb.Day.ToString("00");
                    _mm = _dofb.Month.ToString("00");
                    _yy = _dofb.Year.ToString().Substring(2, 2);

                    _dob = _dsProposal.Tables[0].Rows[0]["DOB"].ToString();
                }

                if (_dsProposal.Tables[2].Rows.Count > 0) //check for 10% promo
                {
                    _havepromo = true;
                }

                _email = _dsProposal.Tables[0].Rows[0]["Email"].ToString();
                _nationality = _dsProposal.Tables[0].Rows[0]["Nationality"].ToString();
                _unitno = _dsProposal.Tables[0].Rows[0]["UnitNo"].ToString();
                _homeno = _dsProposal.Tables[0].Rows[0]["HomeNo"].ToString();
                _address = _dsProposal.Tables[0].Rows[0]["Address"].ToString();
                _postalcode = _dsProposal.Tables[0].Rows[0]["PostalCode"].ToString();
                _nric = _dsProposal.Tables[0].Rows[0]["NRICNo"].ToString();
                _sex = _dsProposal.Tables[0].Rows[0]["Gender"].ToString();
                _email = _dsProposal.Tables[0].Rows[0]["Email"].ToString();

                _planname = _dsProposal.Tables[0].Rows[0]["PlanName"].ToString();
                _plantype = _dsProposal.Tables[0].Rows[0]["PlanType"].ToString();
                _totalpremium = _dsProposal.Tables[0].Rows[0]["TotalPremium"].ToString();

                _hoemphone = _dsProposal.Tables[0].Rows[0]["HomePhoneNo"].ToString();
                _mobile = _dsProposal.Tables[0].Rows[0]["MobileNo"].ToString();
                _officephone = _dsProposal.Tables[0].Rows[0]["OfficePhoneNo"].ToString();
                _faxno = _dsProposal.Tables[0].Rows[0]["FaxNo"].ToString();

                _triptype = _dsProposal.Tables[0].Rows[0]["TripType"].ToString();
                _countryarea = _dsProposal.Tables[0].Rows[0]["Country"].ToString();
                _nooftravellers = _dsProposal.Tables[0].Rows[0]["NoOfTravellers"].ToString();
                _startdate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["StartDate"].ToString());
                _enddate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["EndDate"].ToString());
                _noofadults = _dsProposal.Tables[0].Rows[0]["NoOfAdults"].ToString();
                _noofchilds = _dsProposal.Tables[0].Rows[0]["NoofChilds"].ToString();

                string _cxaccountno = _dsProposal.Tables[0].Rows[0]["CXAccountNo"].ToString();
                string _aucode = _dsProposal.Tables[0].Rows[0]["AuCode"].ToString();

                _html = "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + " (" + _plantype + ")</td>";

                if (_havepromo) //w/ 10% promo
                {
                    _html += "<td colspan='2' align='right'><b>Total Premium</b> : $" + String.Format("{0:0,0.00}", Convert.ToSingle(_totalpremium) * 100 / 90) + "</td>"
                    + "<tr><td width='25%'><b>Quote Reference</b></td><td>TIE" + quoteref + "</td><td colspan='2' align='right'><b>10% FESTIVE PROMO discount</b> $" + String.Format("{0:0,0.00}", Convert.ToSingle(_totalpremium) * 100 / 90 * 0.10) + "</td>"
                    + "<tr><td width='25%'></td><td></td><td colspan='2' align='right'><b>Discounted Premium</b> : $" + String.Format("{0:0,0.00}", Convert.ToSingle(_totalpremium)) + "</td>";
                }
                else
                {
                    _html += "<td colspan='2' align='right'><b>Total Premium</b> : $" + String.Format("{0:0,0.00}", Convert.ToSingle(_totalpremium)) + "</td>"
                   + "<tr><td width='25%'><b>Quote Reference</b></td><td>TIE" + quoteref + "</td>";
                    //+ "<tr><td width='25%'><b>Policy Number</b></td><td>" + _newPolicyNumber + "</td>";
                }

                // _html += "<tr><td width='25%'><b>Payment Reference</b></td><td style='mso-number-format:0' align='left' > " + _orderno + "</td>"
                //+ "<td colspan='2' align='right'> </td>"

                _html += "</tr></table>";
                //_html += "<div><div><b>Details of Proposer</b></div><div style='text-align: center'>"
                _html += "<div><div style='text-align: center'>"
                    + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"
                        + "<tr><td colspan='4'><b>Details of Proposer</b></td></tr></table>"
                        + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"
                        + "<tr>"
                        + "<td width='50%'>" //Start 1
                        + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                        + "<tr>"
                        + "<td width='50%' align='left'>Name(as shown in NRIC / Passport)</td>"
                        + "<td width='50%' align='left'>" + _name.ToString() + "</td>"
                        + "</tr>"

                        + "<tr>"
                        + "<td align='left'>Address</td>"
                        + "<td align='left'>"
                        + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0'>"
                        + "<tr><td align='left'>#" + _unitno.ToString() + " - " + _homeno.ToString() + "</td></tr>"
                        + "<tr><td align='left'>" + _address.ToString() + "</td></tr>"
                        + "<tr><td align='left'>Postal Code " + _postalcode.ToString() + "</td></tr>"
                        + "</table>"
                        + "</td>"
                        + "</tr>"

                         + "<tr>"
                        + "<td width='50%' align='left'>Sex</td>"
                        + "<td width='50%' align='left'>" + _sex.ToString() + "</td>"
                        + "</tr>"

                         + "<tr>"
                        + "<td width='50%' align='left'></td>"
                        + "<td width='50%' align='left'></td>"
                        + "</tr>"


                         + "<tr>"
                        + "<td width='50%' align='left'></td>"
                        + "<td width='50%' align='left'></td>"
                        + "</tr>"


                        + "</table>"
                        + "</td>" //End 1

                        + "<td width='50%'>" //Start 2
                        + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                        + "<tr>"
                        + "<td width='50%' align='left'>Date of Birth</td>"
                        + "<td width='50%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _dob.ToString() + "</td>"
                        + "</tr>"

                        + "<tr>"
                        + "<td width='50%' align='left'>Nationality</td>"
                        + "<td width='50%' align='left'>" + _nationality.ToString() + "</td>"
                        + "</tr>"

                        + "<tr>"
                        + "<td width='50%' align='left'>NRIC Number</td>"
                        + "<td width='50%' align='left'>" + _nric.ToString() + "</td>"
                        + "</tr>"


                        + "<tr>"
                        + "<td width='50%' align='left'>Office No</td>"
                        + "<td width='50%' align='left'>" + _officephone.ToString() + "</td>"
                        + "</tr>"

                        + "<tr>"
                        + "<td width='50%' align='left'>Mobile No</td>"
                        + "<td width='50%' align='left'>" + _mobile.ToString() + "</td>"
                        + "</tr>"

                        + "<tr>"
                       + "<td width='50%' align='left'>Residential No</td>"
                       + "<td width='50%' align='left'>" + _hoemphone.ToString() + "</td>"
                       + "</tr>"

                        + "<tr>"
                        + "<td width='50%' align='left'>Email Address </td>"
                        + "<td width='50%' align='left'>" + _email.ToString() + " </td>"
                        + "</tr>"

                        + "</table>"
                        + "</td>" //End 2
                        + "</tr>"

                        + "<tr>"
                        + "<td colspan='2' align='left'></td>"
                        + "</tr>"

                        + "<tr>"
                        + "<td colspan='2'>";

                //TRAVEL DETAILS
                _html += "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"
                       + "<tr><td colspan='4' align='left'><b>Travel Details </b></td></tr>"
                       + "<tr><td style='width:25%' align='left'>Area/Country of Destination</td>"
                       + "<td style='width: 25%' align='left'>" + _countryarea + "</td>"
                       + "<td style='width: 25%' align='left'>Trip Type</td>"
                       + "<td style='width: 25%' align='left'>" + GetTripType(_triptype) + "</td></tr>";

                string periodofinsurance = "";
                if (_triptype == "S")
                {
                    periodofinsurance = _startdate.ToString("dd-MMM-yyyy") + " to " + _enddate.ToString("dd-MMM-yyyy");
                }
                else
                {
                    periodofinsurance = "(start from) " + _startdate.ToString("dd-MMM-yyyy");
                }

                _html += "<tr><td style='width: 25%' align='left' >No of Travellers</td>"
                      + "<td align='left' colspan='3'>" + _nooftravellers + "</td></tr>"
                      + "<tr><td style='width:25%' align='left'>Period of Insurance</td>"
                      + "<td align='left' colspan='3'>" + periodofinsurance + "</td></tr>"
                      + "<tr><td colspan='4' align='left'> " + " " + "</td></tr>";

                //Travelers to Insure
                _html += "<tr><td colspan='4' align='left'><b>Travellers to Insure</b></td></tr>";
                string InsuredHTML = "";
                if (_dsProposal.Tables[1].Rows.Count > 0)
                {
                    for (int j = 0; j < _dsProposal.Tables[1].Rows.Count; j++)
                    {
                        if (j == 0)
                        {
                            _insname = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                            _insnric = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                            DateTime _insdate = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                            _insdob = _insdate.Day.ToString("00") + "/" + _insdate.Month.ToString("00") + "/" + _insdate.Year.ToString("0000");
                            _insgender = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                            // _insoccupation = _dsProposal.Tables[2].Rows[j]["Occupation"].ToString();
                            _insrelation = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                            _insnation = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                            InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>Full Name</td>"
                                       + "<td  width='25%' align='left' >" + _insname + "</td>"
                                       + "<td style='width:25%' align='left'>NRIC No.</td>"
                                       + "<td  style='width:25%' align='left'>" + _insnric + "</td></tr>"

                                        + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                        + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _insdob + "</td>"
                                        + "<td style='width:25%' align='left'>Gender</td>"
                                        + "<td style='width:25%' align='left'>" + _insgender + "</td></tr>"

                                        + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                        + "<td style='width:25%' align='left'>" + _insnation + "</td>"
                                        + "<td style='width:25%' align='left'>Relation</td>"
                                        + "<td style='width:25%' align='left'>" + _insrelation + "</td></tr>"

                                        + "<tr><td style='width:25%' align='left'></td>"
                                        + "<td style='width:25%' align='left'></td>"
                                        + "<td style='width:25%' align='left'></td>"
                                        + "<td style='width:25%' align='left'></td></tr>"

                                        + "<tr><td colspan='4' align='left'></td></tr>";

                        }
                        if (Convert.ToInt32(_noofadults) > 1)
                        {
                            if (j == 1)
                            {
                                _sponame = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _sponric = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _spodate = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _spodob = _spodate.Day.ToString("00") + "/" + _spodate.Month.ToString("00") + "/" + _spodate.Year.ToString("0000");
                                _spogender = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                // _spooccupation = _dsProposal.Tables[2].Rows[j]["Occupation"].ToString();
                                _sporelation = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _sponation = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>Adult2 (or) Spouse Full Name</td>"
                                           + "<td  width='25%' align='left' >" + _sponame + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _sponric + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _spodob + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _spogender + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _sponation + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _sporelation + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'></td>"
                                            + "<td style='width:25%' align='left'></td>"
                                            + "<td style='width:25%' align='left'></td>"
                                            + "<td style='width:25%' align='left'></td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 2)
                            {
                                InsuredHTML = InsuredHTML + "<tr><td colspan='4' align='left'><b>Child Details</b></td></tr>";
                                _childname1 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric1 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate1 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob1 = _childate1.Day.ToString("00") + "/" + _childate1.Month.ToString("00") + "/" + _childate1.Year.ToString("0000");
                                _childgender1 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation1 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation1 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>1. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname1 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric1 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob1 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender1 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation1 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation1 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 3)
                            {
                                _childname2 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric2 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate2 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob2 = _childate2.Day.ToString("00") + "/" + _childate2.Month.ToString("00") + "/" + _childate2.Year.ToString("0000");
                                _childgender2 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation2 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation2 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>2. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname2 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric2 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob2 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender2 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation2 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation2 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 4)
                            {
                                _childname3 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric3 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate3 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob3 = _childate3.Day.ToString("00") + "/" + _childate3.Month.ToString("00") + "/" + _childate3.Year.ToString("0000");
                                _childgender3 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation3 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation3 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>3. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname3 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric3 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob3 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender3 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation3 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation3 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 5)
                            {
                                _childname4 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric4 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate4 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob4 = _childate4.Day.ToString("00") + "/" + _childate4.Month.ToString("00") + "/" + _childate4.Year.ToString("0000");
                                _childgender4 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation4 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation4 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>4. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname4 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric4 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob4 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender4 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation4 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation4 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 6)
                            {
                                _childname5 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric5 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate5 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob5 = _childate5.Day.ToString("00") + "/" + _childate5.Month.ToString("00") + "/" + _childate5.Year.ToString("0000");
                                _childgender5 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation5 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation5 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>5. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname5 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric5 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob5 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender5 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation5 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation5 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 7)
                            {
                                _childname6 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric6 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate6 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob6 = _childate6.Day.ToString("00") + "/" + _childate6.Month.ToString("00") + "/" + _childate6.Year.ToString("0000");
                                _childgender6 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation6 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation6 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>6. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname6 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric6 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob6 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender6 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation6 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation6 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                        }
                        else
                        {
                            if (j == 1)
                            {
                                InsuredHTML = InsuredHTML + "<tr><td colspan='4' align='left'><b>Child Details</b></td></tr>";
                                _childname1 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric1 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate1 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob1 = _childate1.Day.ToString("00") + "/" + _childate1.Month.ToString("00") + "/" + _childate1.Year.ToString("0000");
                                _childgender1 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation1 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation1 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>1. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname1 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric1 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob1 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender1 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation1 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation1 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 2)
                            {
                                _childname2 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric2 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate2 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob2 = _childate2.Day.ToString("00") + "/" + _childate2.Month.ToString("00") + "/" + _childate2.Year.ToString("0000");
                                _childgender2 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation2 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation2 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>2. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname2 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric2 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob2 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender2 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation2 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation2 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 3)
                            {
                                _childname3 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric3 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate3 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob3 = _childate3.Day.ToString("00") + "/" + _childate3.Month.ToString("00") + "/" + _childate3.Year.ToString("0000");
                                _childgender3 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation3 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation3 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>3. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname3 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric3 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob3 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender3 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation3 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation3 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 4)
                            {
                                _childname4 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric4 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate4 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob4 = _childate4.Day.ToString("00") + "/" + _childate4.Month.ToString("00") + "/" + _childate4.Year.ToString("0000");
                                _childgender4 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation4 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation4 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>4. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname4 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric4 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob4 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender4 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation4 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation4 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 5)
                            {
                                _childname5 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric5 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate5 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob5 = _childate5.Day.ToString("00") + "/" + _childate5.Month.ToString("00") + "/" + _childate5.Year.ToString("0000");
                                _childgender5 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation5 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation5 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>5. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname5 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric5 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob5 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender5 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation5 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation5 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                            if (j == 6)
                            {
                                _childname6 = _dsProposal.Tables[1].Rows[j]["FullName"].ToString();
                                _childnric6 = _dsProposal.Tables[1].Rows[j]["IdentificationNum"].ToString();
                                DateTime _childate6 = Convert.ToDateTime(_dsProposal.Tables[1].Rows[j]["Birthday"].ToString());
                                _childdob6 = _childate6.Day.ToString("00") + "/" + _childate6.Month.ToString("00") + "/" + _childate6.Year.ToString("0000");
                                _childgender6 = _dsProposal.Tables[1].Rows[j]["Gender"].ToString();
                                _childrelation6 = _dsProposal.Tables[1].Rows[j]["RelationshipToProposer"].ToString();
                                _childnation6 = _dsProposal.Tables[1].Rows[j]["Nationality"].ToString();
                                InsuredHTML = InsuredHTML + "<tr><td style='width:25%' align='left'>6. Child Name</td>"
                                           + "<td  width='25%' align='left' >" + _childname6 + "</td>"
                                           + "<td style='width:25%' align='left'>NRIC No.</td>"
                                           + "<td  style='width:25%' align='left'>" + _childnric6 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Date of Birth</td>"
                                            + "<td style='width:25%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _childdob6 + "</td>"
                                            + "<td style='width:25%' align='left'>Gender</td>"
                                            + "<td style='width:25%' align='left'>" + _childgender6 + "</td></tr>"

                                            + "<tr><td style='width:25%' align='left'>Nationality</td>"
                                            + "<td style='width:25%' align='left'>" + _childnation6 + "</td>"
                                            + "<td style='width:25%' align='left'>Relation</td>"
                                            + "<td style='width:25%' align='left'>" + _childrelation6 + "</td></tr>"

                                            + "<tr><td colspan='4' align='left'></td></tr>";
                            }
                        }
                    }
                }
                _html = _html + InsuredHTML;


                _html += " "

                + "<tr><td colspan='4' align='left'></td></tr>"
                + "<tr><td>Account No.</td><td>[" + _cxaccountno + "]</td><td>Certificate No.</td><td>[" + _newPolicyNumber + "]</td></tr>"
                + "<tr><td>Order No.</td><td>[" + _orderno + "]</td><td>AuCode</td><td>[" + _aucode + "]</td></tr>"

                + "</table>"
                + "</td>"
                + "</tr>"

                + "</table>"
                + "</div></div>";
            }

            lblContent.Text = _html;
            MyIO myio = new MyIO();

            string[] filecontent = new string[1];
            filecontent[0] = strText + lblContent.Text.ToString();

            //_filePath = GetFilePath();
            myio.WriteNewFile(FilePath, FileName + ".xls", filecontent);
            _returnFilePath = FilePath + FileName + ".xls";

        }
        catch (Exception Ex)
        {
            Ex = null;
        }

        return _returnFilePath;
    }

    private string GetTripType(string triptype)
    {
        string TripType = "Single Trip";

        if (triptype == "A")
            TripType = "Annual Multi-Trip";

        return TripType;

    }

    private int ComputeDuration(DateTime dtFrom, DateTime dtTo)
    {
        int TripDuration = 0;
        TripDuration = Convert.ToInt32(dtTo.Subtract(dtFrom).TotalDays.ToString());
        TripDuration = TripDuration + 1;

        return TripDuration;
    }
}
